many-to-many SQL Query

I have created a many-to-many database...

<pre id=code><font face=courier size=2 id=code>
Categories Company Join_Cat
--------- --------- --------
Cat_ID* Comp_ID* Join_ID*
Cat_Name Comp_Name Cat_ID
Comp_ID
</font id=code></pre id=code>


The Join_Cat table joins the Company to various categories.

What I am trying to do is return all categories that a company IS NOT listed in ...
(or, filter out the categories that they are already in)

...I can return all Categories that Company 1 are in. e.g...

<pre id=code><font face=courier size=2 id=code>
SELECT * FROM Categories
WHERE Cat_ID IN
(SELECT DISTINCT Join_Cat.Cat_ID FROM Join_Cat WHERE Join_Cat.Comp_ID = 1)
</font id=code></pre id=code>

But, when I try to reverse the results to 'not equal to' (&lt;&gt; 1) the query returns all Categories. I'd thought this would return all categories that company 1 are not in)

I have tried joins and Subqueries, but to no avail.

Any help would be appreciated.

SERVER MODEL:
PHP and MySQL v5

Edited by - markevans147 on 25 Jan 2006 18:00:07

Hi Mark

Try:

WHERE NOT Cat_ID IN

Sharing Knowledge Saves Valuable Time!!!
~ ~ ~ ~ ~ ~
<b>Lee Diggins</b> - <i>DMXzone Manager</i>
<font size="1">[ Studio MX/MX2004 | ASP -> VBScript/PerlScript/JavaScript | SQL | CSS ]</font>

Thanks for that - it works . . .
Can you help me convert the subquery to a join?
I need it to be compatable with MySQL version 4.0.

<pre id=code><font face=courier size=2 id=code>SELECT * FROM Categories WHERE Cat_ID NOT IN
(SELECT DISTINCT Join_Cat.Cat_ID FROM Join_Cat WHERE Join_Cat.Comp_ID = 1)</font id=code></pre id=code>