MySQL query

[dionyssos]

Hi everybody,

I want to build a query that returns records that are not joined. i.e.

SELECT items.item_id, title, date
FROM items, itempics
WHERE items.item_id NOT itempics.item_id

But this does not work. Can anyone tell me what does work?

Thanx in advance,

Dionyssos

[tomhath]

SELECT items.item_id, title, date
FROM items
WHERE NOT EXISTS (
select * from itempics
where items.item_id = itempics.item_id )

[edup_pt]

I think you cannot do select from a select in Mysql......

[[deleted]]

Read the MYSQL manual about LEFT JOIN.

SELECT items.item_id, title, date
FROM items LEFT JOIN itempics ON items.item_id = itempics.item_id
WHERE itempics.item_id IS NULL;

[tomhath]

You are right, mysql cannot do subselects. I am so used to SQLServer I forgot to check for things I use every day that are not implemented in mysql. Vincent gives the correct alternative to the subselect from the mysql manual in his post below.

[dionyssos]

It worked! =) And the best part is(for me at least) that I also understand why this code works and the code I wrote doesn't. Can't thank you guys enough!

Dionyssos

[[deleted]]

Hehe.. I know the feeling... try working with PostgreSQL fora few days and then go back to MySQL.. aaarg! 🙂