Hi there,

I'm trying to compare 2 arrays at the moment. There are 2 tables, the first (Category) holds a list of categories, e.g. Electricians, Plumbers, Builders and each has an ID number.
The second table (Company) is a list of companies e.g. JonTheElectrician, GaryThePlumber and of course BobTheBuilder. Each company has a category assigned to it.
What i want to do is search 'Company' find all the catgories that are used then compare that to 'Category' and print to a dropdown box which ones haven't been used.

I've looked everywhere for a decent example. I used DISTINCT to get a list of used categories within 'Company' and tried to adapt examples of LEFT (OUTER) JOIN and array_diff to suit my needs but can't seem to get anywhere.

Any help much appreciated,

Tom.

Have you tried the "NOT IN" statement?

example:

SELECT DISTINCT Category.name FROM Category
WHERE category.id NOT IN 
(SELECT Company.category FROM Company)

ooh havent heard of that one will give it a try cheers

That worked a treat thanks very much,

Can't believe i'd never heard of the NOT IN command,

Cheers,

Tom.