Jump to content


Photo

Need help with SQL Query


  • Please log in to reply
2 replies to this topic

#1 pwdrskier4

pwdrskier4
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 31 May 2006 - 06:21 PM

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?

SELECT mls.mls_num, geodata.geodata_mls
FROM mls, geodata
WHERE mls.mls_num != geodata.geodata_mls
ORDER BY mls_num ASC


#2 reandeau

reandeau
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 31 May 2006 - 07:04 PM

[!--quoteo(post=378795:date=May 31 2006, 11:21 AM:name=bill444)--][div class=\'quotetop\']QUOTE(bill444 @ May 31 2006, 11:21 AM) View Post[/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?

SELECT mls.mls_num, geodata.geodata_mls
FROM mls, geodata
WHERE mls.mls_num != geodata.geodata_mls
ORDER BY mls_num ASC
[/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

#3 pwdrskier4

pwdrskier4
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 31 May 2006 - 08:46 PM

[!--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) View Post[/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!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users