how to join two db tables and cross reference another

Question

I'm trying to figure out an efficient way to join tables and cross reference with another table, with the reference looking to check that each record found in the join does not have a field which exists in the other table.

This is what I have so far - I somehow need to be able to make sure that each record's reg_no is not present in the buyers table (basically before an automated email is sent). Grateful for any pointers - anything that saves me having to iterate through each record and do an individual lookup!

SELECT * FROM (`owners`, `buyers`) 
 JOIN `records` ON (`records`.`pa_no` = `owners`.`contact_no`) 
 WHERE email <> "" AND `buyers`.reg_no <> `records`.reg_no

Answer 1

Try:

select * from `owners`
join `records` ON (`records`.`pa_no` = `owners`.`contact_no`)
WHERE email <> ""
and not exists (select reg_no from `buyers` 
                where `buyers`.reg_no = `records`.reg_no)

Answer 2

Do you mean like a LEFT JOIN?

SELECT r.reg_no
  FROM records as r LEFT JOIN buyers as b ON (r.reg_no = b.reg_no)
  WHERE b.reg_no IS NULL

A LEFT JOIN between tables A and B will give you a table with at least one row for every row in A. With a normal JOIN, rows in A where the associated row in B was not found will be omitted; for a LEFT JOIN these rows will be included, but all of B's columns will simply have NULL values. So you can test a field that can never be NULL (like the primary key or foreign key): if it's NULL then you're certain this row (of A) do not have an associated row in B.

And if I understand your question correctly, this is what you want -- a table of all the rows in records (together with owner information) that has no associated buyer information.

So, you could either use something like the above as a subquery or view, and JOIN it normally with owner, or you could do it all in one (I believe MySQL should be intelligent enough to optimize it so these approaches are equivalent):

SELECT * -- though probably you want to specify what columns you want
  FROM (owners o JOIN records r ON (o.contact_no=r.pa_no))
    LEFT JOIN buyers b ON (r.reg_no=b.reg_no)
  WHERE email<>'' AND b.reg_no IS NULL

(I'm just typing it in as I think it might work, I haven't tested this. Please correct me if I'm wrong.)