Member Avatar for baseballfury

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.

  • 2 Contributors
  • 3 Replies
  • 170 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by baseballfury
Member Avatar for Lsmjudoka

Have you tried the "NOT IN" statement?

example:

SELECT DISTINCT Category.name FROM Category
WHERE category.id NOT IN 
(SELECT Company.category FROM Company)
Member Avatar for baseballfury

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

Member Avatar for baseballfury

That worked a treat thanks very much,

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

Cheers,

Tom.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.