Need help with SQL Query

[pwdrskier4]

I have two tables (mls and geodata) that each have mls numbers as primary key. I am trying to return all off the records in table mls where the mls number does not have a row in geodata. My current statement is returning too many records. I have 8 records in mls and 3 records in geodata (all of which match an mls # in table mls) so I should get 5 records returned but instead get 21 (8 * 3 minus the 3 matches). Can anyone help?

[code]SELECT mls.mls_num, geodata.geodata_mls
FROM mls, geodata
WHERE mls.mls_num != geodata.geodata_mls
ORDER BY mls_num ASC[/code]

[reandeau]

[!--quoteo(post=378795:date=May 31 2006, 11:21 AM:name=bill444)--][div class=\'quotetop\']QUOTE(bill444 @ May 31 2006, 11:21 AM) [snapback]378795[/snapback][/div][div class=\'quotemain\'][!--quotec--]
I have two tables (mls and geodata) that each have mls numbers as primary key. I am trying to return all off the records in table mls where the mls number does not have a row in geodata. My current statement is returning too many records. I have 8 records in mls and 3 records in geodata (all of which match an mls # in table mls) so I should get 5 records returned but instead get 21 (8 * 3 minus the 3 matches). Can anyone help?

[code]SELECT mls.mls_num, geodata.geodata_mls
FROM mls, geodata
WHERE mls.mls_num != geodata.geodata_mls
ORDER BY mls_num ASC[/code]
[/quote]


Try something like this:
SELECT mls.mls_num FROM mls LEFT JOIN geodata ON (mls.mls_num = geodata.geodata_mls) WHERE geodata.geodata_mls is NULL;

Let me know how it goes.

Jon Tjemsland

[pwdrskier4]

[!--quoteo(post=378806:date=May 31 2006, 01:04 PM:name=Jon Tjemsland)--][div class=\'quotetop\']QUOTE(Jon Tjemsland @ May 31 2006, 01:04 PM) [snapback]378806[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Try something like this:
SELECT mls.mls_num FROM mls LEFT JOIN geodata ON (mls.mls_num = geodata.geodata_mls) WHERE geodata.geodata_mls is NULL;

[/quote]

That totally worked, thank you so much. I actually had already solved my issue by dumping the #'s from the geodata table into an array and then comparing them to the #'s from mls table but this is much simpler. Thanks!