Thread: self join

Hi,

This probably reflects my confusion with how self joins work.

Suppose we have this table:

=# SELECT * FROM tmp;a | b 
---+---1 | 22 | 34 | 5
(3 rows)

If I want to get a table with records where none of the values in column
b are found in column a, I thought this should do it:

=# SELECT * FROM tmp t1, tmp t2 WHERE t2.b <> t1.a;a | b | a | b 
---+---+---+---1 | 2 | 1 | 21 | 2 | 2 | 31 | 2 | 4 | 52 | 3 | 2 | 32 | 3 | 4 | 54 | 5 | 1 | 24 | 5 | 2 | 34 | 5 | 4 |
5
(8 rows)

I need to get:
a | b | a | b 
---+---+---+---1 | 2 | 1 | 24 | 5 | 4 | 5

Or just:
a | b  
---+---1 | 2 4 | 5 


-- 
Seb

Hi

2011/5/15 Seb <spluque@gmail.com>:
> Hi,
>
> This probably reflects my confusion with how self joins work.
>
> Suppose we have this table:
>
> =# SELECT * FROM tmp;
>  a | b
> ---+---
>  1 | 2
>  2 | 3
>  4 | 5
> (3 rows)
>
> If I want to get a table with records where none of the values in column
> b are found in column a, I thought this should do it:
>
> =# SELECT * FROM tmp t1, tmp t2 WHERE t2.b <> t1.a;
>  a | b | a | b
> ---+---+---+---
>  1 | 2 | 1 | 2
>  1 | 2 | 2 | 3
>  1 | 2 | 4 | 5
>  2 | 3 | 2 | 3
>  2 | 3 | 4 | 5
>  4 | 5 | 1 | 2
>  4 | 5 | 2 | 3
>  4 | 5 | 4 | 5
> (8 rows)
>
> I need to get:
>
>  a | b | a | b
> ---+---+---+---
>  1 | 2 | 1 | 2
>  4 | 5 | 4 | 5
>
> Or just:
>
>  a | b
> ---+---
>  1 | 2
>  4 | 5

Your query doesn't have an explicit join and is producing a cartesian result.

I don't think a self- join will work here; a subquery should produce the
result you're after:

SELECT * FROM tmp t1WHERE NOT EXISTS(SELECT TRUE FROM tmp t2 WHERE t2.b=t1.a);


HTH


Ian Lawrence Barwick

On Sun, 15 May 2011 07:39:06 +0900,
Ian Lawrence Barwick <barwick@gmail.com> wrote:

[...]

> Your query doesn't have an explicit join and is producing a cartesian
> result.

> I don't think a self- join will work here; a subquery should produce
> the result you're after:

> SELECT * FROM tmp t1 WHERE NOT EXISTS(SELECT TRUE FROM tmp t2 WHERE
> t2.b=t1.a);

This produces exactly the result I'm after.  I'll need to understand the
EXISTS statement there in more detail.

Thanks!

-- 
Seb

On 2011-05-14, Seb <spluque@gmail.com> wrote:
> Hi,
>
> This probably reflects my confusion with how self joins work.
>
> Suppose we have this table:

> If I want to get a table with records where none of the values in column
> b are found in column a, I thought this should do it:

use the "NOT IN" operator with a subquery to retch the disallowed
values.

select * from tmp where a NOT IN (select b from tmp);

-- 
⚂⚃ 100% natural

In article <iqne7u$ssa$2@reversiblemaps.ath.cx>,
Jasen Betts <jasen@xnet.co.nz> writes:

> On 2011-05-14, Seb <spluque@gmail.com> wrote:
>> Hi,
>> 
>> This probably reflects my confusion with how self joins work.
>> 
>> Suppose we have this table:

>> If I want to get a table with records where none of the values in column
>> b are found in column a, I thought this should do it:

> use the "NOT IN" operator with a subquery to retch the disallowed
> values.

> select * from tmp where a NOT IN (select b from tmp);

The third way is an OUTER JOIN:

SELECT t1.a, t1.b
FROM tmp t1
LEFT JOIN tmp t2 ON t2.b = t1.a
WHERE t2.b IS NULL;

On 05/14/2011 07:36 PM, Jasen Betts wrote:
>
> use the "NOT IN" operator with a subquery to retch the disallowed
> values....
Hmmm, "retch" as a synonym for "output"? I've seen more than one case 
where that is an appropriate description. :)

Cheers,
Steve

On 2011-05-16, Steve Crawford <scrawford@pinpointresearch.com> wrote:
> On 05/14/2011 07:36 PM, Jasen Betts wrote:
>>
>> use the "NOT IN" operator with a subquery to retch the disallowed
>> values....
> Hmmm, "retch" as a synonym for "output"? I've seen more than one case 
> where that is an appropriate description. :)
:)  was a typo for 'fetch' 


-- 
⚂⚃ 100% natural