query 2 tables?

[adamboalt]

I currently have two tables which I want to query at the same time.

I have one table which contains zip codes. Any zip code in this table means that it is taken and not available.

The other table is a waiting list of people who wanted to reserve the zip code at some time or another but could not because it was not available.

I want to send an email to the people who are waiting for a zip code when it becomes available.

So I would have to query all the zip codes in the waiting list against the zip code table - if any are not present in the zip code table that would mean they are available.

My reservation table is called: res_list

res_list

res_id int(20)

res_date date

res_name varchar(100)

res_email varchar(100)

res_zip int(5)

report varchar(75) -- Seller or Buyer

My taken zip code table is called: zip_codes

zip_codes

account_num int(15)

zip_code int(5)

report varchar(20)
reserved_date

So I would want to send a notification email to the people on the waiting list.

I hope this makes sense.

Thanks in advance for your help.

Adam

[nnichols]

SELECT r.*
FROM res_list r
LEFT JOIN zip_codes z
ON r.res_zip = z.zip_code
WHERE z.zip_code IS NULL

Hope this helps 😉

[adamboalt]

It worked great! Thanks a million.
One quick question.

How do I setup same statement to show the report field.

For example report is either equals "Buyer" or "Seller"

So if only the "Buyer" is available _ I would only want to query buyers and vice versa. If both are available then both.

Thanks,
Adam

[nnichols]

The query I wrote retrieves all columns of the "res_list" table so it should show the "report" column.

If this is not what you mean please post some sample data in the form of the data for the two tables and the data you want retrieved.