Jump to content


Photo

[SOLVED] SQL Help


  • Please log in to reply
2 replies to this topic

#1 tobeyt23

tobeyt23
  • Members
  • PipPipPip
  • Advanced Member
  • 259 posts
  • LocationSalisbury

Posted 17 January 2006 - 04:03 PM

I am trying to pull only the zips that there ids are not in the zip_tracker table, but when i do so it only sees the first enter that is in the zip_tracker table and show the rest along with everything else that should be shown. The purpose is someone looks up a zip selects it and i place it in the zip_tracker table so when someone else looks up the zip again they can not select it. What is wrong with this:

SELECT zip_code.* FROM zip_code RIGHT JOIN zip_tracker on zip_code.zip_code_id!=zip_tracker.zip_code_id and zip_tracker.mailer_date='0000-00-00' WHERE zip_code LIKE '0%'


#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 17 January 2006 - 05:25 PM

First, you want a LEFT JOIN, not a RIGHT JOIN. Second, if you want records that do _not_ show up in the "right" table, you need to add a "WHERE rightTable.id IS NULL". Third, you'd need to change your ON clause to have an equality relating the primary key to the foreign key. Fourth, I'm not sure what you mean by the first number -- that's in your WHERE clause already.

The following query (UNTESTED) will find the "non-tracked" zip codes:

SELECT zip_code.* FROM zip_code LEFT JOIN zip_tracker ON zip_code.zip_code_id=zip_tracker.zip_code_id WHERE zip_tracker.zip_code_id IS NULL

Feel free to add whatever other clauses are necessary.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 tobeyt23

tobeyt23
  • Members
  • PipPipPip
  • Advanced Member
  • 259 posts
  • LocationSalisbury

Posted 17 January 2006 - 05:36 PM

thanks!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users