mySQL statement problem

Peter B

I have two tables

table1
id colour

---

+

---

+
46 red
54 blue
23 blue
28 black

table2
id category

---

+

---

+
{Empty}

table2 is an empty table

I want to select all ids in table1 which are not in table2.

How do you do this?

I have

select * from table1 where (colour = 'black' and (table1.id!=ANY(Select id from table2)))

This returns empty set which is not the answer I was looking for.

Any assistance?

seamus

Try
SELECT table1.* FROM table1 JOIN table2 ON table1.id != table2.id

Peter B

Thanks seamus but still returns an empty set.....

bonkey

I assume you've tried teh obvious and removed the check against colour to ensure that the error/problem must be with the id comparison?

I'm no mySQL expert, but if your colour is stored in the table as, for example, 'black ', then the query is behaving entirely correctly as it stands.

Does MySQL (or this version thereof) support OUTER JOIN syntax? If so, what you want is :

... FROM table1 LEFT OUTER JOIN table2 on table1.id = table2.id WHERE ...

jc

madramor

SELECT * FROM table1
WHERE
id NOT IN (SELECT id FROM table2);

or older versions

SELECT table1.id FROM table1
LEFT JOIN table2
ON
table1.id=table2.id
WHERE
table2.id IS NULL;

madramor

thats straight out of the MySql docs version 5
chapter 14.1.8

Peter B

madramor, think thats done it,

Thanks