MySql join

BlueSpud

I am a novice sql user & cant figure out how to do the following in MySql sql (or any other sql for that matter);

Product

---

id
name

Order

---

id
prod_id (f/key)
qty

I want to return the list of products that do not have orders against them.

Tom Dunne

What have you tried?

BlueSpud

Well thats my problem, I dont know how to begin to express the problem, never mind try something.

My knowlege extends only to joining A and B, C, D with inner & left joins and a where clause.

fasty

You don't need to do a join per se. Think about what you want...

You want all products that are not in the orders table. Your where clause can be a sub query.

BlueSpud

Just got the following to work(plagerised from the web). I understand that it is more efficient than the subquery, or so the article says.

SELECT p.id, p.name
FROM prod p
LEFT JOIN ord AS o ON o.prod_id = p.id
WHERE o.prod_id IS NULL

nialo

Alternative way of doing it. What you have is fine too thou..

select p.id, p.name from Product p where p.id not in (select distinct prod_id from Orders)

BlueSpud

The reason I struggled for so long to get it working was that I was using
blah = null
and not
blah is null

Thanks for the help.

seamus

BlueSpud wrote: »

The reason I struggled for so long to get it working was that I was using
blah = null
and not
blah is null

Thanks for the help.

Yeah, NULL is a special state in most databases. It's not a value, in fact it specifically means "Has no value". This differs from having a blank in the database, which does in fact have a value, it's just blank. So you can't check if something is equal to NULL (because NULL is not a value), you can only check if it is in the NULL state.

If you start using a scripting language like PHP, it'll screw with your head when you find out that PHP treats NULL and blank as the same thing when you're reading the database data.