Opposite to INNER JOIN query

crisan · April 29th, 2005, 06:18 AM

I have to tables, tbl1 and tbl2 which of them with an ID field. I need a query which return me in qryID1 row only the ID's from tbl1 without a correspondence in tbl2 and in qryID2 row only the ID's from tbl2 without correspondence in tbl1. Thank you!

rjweers · April 29th, 2005, 06:50 AM

An easy way to do this is to UNION two queries.

The two queries will be something like (I'm writing this on the fly so my syntax may not be exact):

Code:

SELECT * from tbl1 
LEFT JOIN tbl2 on tbl1.ID = tbl2.ID
WHERE tbl2.ID is Null

SELECT * from tbl2
LEFT JOIN tbl1 on tbl2.ID = tbl1.ID
WHERE tbl1.ID is null

To create a UNION query: create a query without any tables. Select the menu option Query | SQL Specific | Union.

The SQL is:

Code:

Select[listoffields] from qry1
UNION
Select [fieldsthatpairwith_listoffields] FROM qry2;

Of course you can roll that all into one query:

Code:

SELECT[listoffields] from tbl1 
LEFT JOIN tbl2 on tbl1.ID = tbl2.ID
WHERE tbl2.ID is Null
UNION
SELECT [fieldsthatpairwith_listoffields] from tbl2
LEFT JOIN tbl1 on tbl2.ID = tbl1.ID
WHERE tbl1.ID is null

Each SELECT statement in UNION must have the same number of fields as all other SELECT statements.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org