Help with Query

[WP]

I have two tables that share the same key field (ID).&nbsp;&nbsp;I need to select all records in the first table (Table1) where there is no match in the second table.<br><br>Can anyone help with the SQL for this.<br><br><br>WP <p>Bill Paton<br><a href=mailto:william.paton@ubsw.com>william.paton@ubsw.com</a><br><a href=

http://www.neptune400.co.uk>www.neptune400.co.uk</a><br>

 

[JARE]

SELECT DISTINCTROW TABLE1.KEY FROM TABLE1 LEFT JOIN TABLE2 ON TABLE1.KEY = TABLE2.KEY2 WHERE TABLE2.KEY2 Is Null;<br><br><br>TABLE 1 WOULD BE YOUR FIRST TABLE WITH KEY BEING THE FIELD<br>TABLE 2 WOULD BE YOUR SECOND WITHOUT MATCHING RECORDS

 

[JARE]

IF YOU NEED MORE THAN THE KEY FIELD DISPLAYED,<br>JUST ADD THE OTHER FIELDS FROM THE 1ST TABLE<br>BETWEEN DISTINCT ROW AND FROM.<br><br>SELECT DISTINCTROW TABLE1.KEY, TABLE1.FIELD1, TABLE1.FIELD2, TABLE1.FIELD3 FROM TABLE1 LEFT JOIN TABLE2 ON TABLE1.KEY = TABLE2.KEY2 WHERE TABLE2.KEY2 Is Null;<br><br><br>

 

[tsman]

Another way to see this is to design a Query.&nbsp;&nbsp;Join the 2 tables together and double click the join line.&nbsp;&nbsp;Change it to select all from table 1 and only matching from table 2.&nbsp;&nbsp;then on the field selection select all fields you need from table 1 and only the key from table 2.&nbsp;&nbsp;Uncheck the key from table 2 and place in the criteria (Is Null).&nbsp;&nbsp;Then switch to SQL mode and copy the SQL to your code.