Cross table select

gammaster

I have problems with a query that involves three tables.

Table contacts: contact_id, contact_name

Table companies: company_id, company_name

Table ref_company_contacts: ref_id, contact_id, company_id

If i want to get contacts for a company the query goes like this:

$companyId = 3;

$sql = "SELECT contacts.* FROM contacts, companies, ref_company_contacts";
$sql.= " WHERE companies.company_id = '$companyId'";
$sql.= " AND companies.company_id = ref_company_contacts.company_id";
$sql.= " AND ref_company_contacts.contact_id = contacts.contact_id";

But what shall i do if i want to select only the contacts in table "contacts" that not have a referanse to the company in table "ref_company_contacts".

Hope someone can help me.

Sxooter

Anytime you want all of something in one table that has no matchin in another table, a left join and where null clause will work well, and is faster than a not in() clause, (if your db supports them)

select * from table1, left join table2 on (table1.field=table2.field) where table2.field IS NULL

gammaster

Hi Sxooter and thx for your reply.

With two tables your description works fine, but in my case i can`t figure out how to use it. You say:

all of something in one table that has no matching in another table

In my case one contact or company can have matches in the "ref_company_contacts" table, but in a other combinations.

With words:
I want to select all contacts that not have a referanse to the spesific company. But the company and the contact can still be represented in the "ref_company_contacts" table.

Im sure that your way is the one, but I need moore help to see the solution.

gammaster

I resolved my problem based on your description(after some headache).

My query then goes like this:

$companyId = 3;

$sql = "SELECT contacts.* FROM contacts  LEFT JOIN ref_company_contacts ON";
$sql.= " (ref_company_contacts.company_id = '$companyId'";
$sql.= " AND ref_company_contacts.contact_id = contacts.contact_id)";
$sql.= " WHERE ref_company_contacts.company_id IS NULL";

Tanks.