easy join? 1

[michelleqw]

Dear sql user,

We are trying to make a join of two tables:

tblX tbly

nr nr
1 3
2
3
4

The result of the join is:

1
2
4

We want to see the result what tbly is missing!

Can someone give us the join?

Nice regards,

Michelle.

 

[r937]

select tblX.nr
  from tblX
left outer
  join tblY
    on tblX.nr 
     = tblY.nr
 where tblY.nr is null

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[michelleqw]

Hello r937,

Thanks a lot!

Michelle.

 

[JarlH]

Same result:

SELECT nr
FROM tblX
WHERE NOT EXISTS (SELECT *
FROM tblY
WHERE tblX.nr = tblY.nr);

 

[PHV]

Another way:
SELECT nr
FROM tblX
WHERE nr NOT IN (SELECT nr FROM tblY)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[r937]

another way:

select nr from tblX
except
select nr from tblY

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)