products / product groups issue

[digidrew]

Hey guys,

Just hoping someone can help me out here. I´ve been stuck on this one for a while. Having a look at the following tables, using sql just wondering how I could select all products that are in the "laptops" group and also in the "512mb" group at the same time.

The following sql doesn´t work obviously, but I´m sure there must be some way to do this...

or is my database structure incorrect?

please help,

thanks,

andrew

SELECT products.name
FROM products,groups,product_groups
WHERE products.id = product_groups.product_id
AND product_groups.group_id = groups.id

AND groups.id = 2 AND groups.id = 4

products
---------------------
¦id ¦ name ¦
---------------------
¦1 ¦ xps 710 ¦
¦2 ¦ Inspirion 1300 ¦
---------------------

groups
----------------
¦id ¦ name ¦
----------------
¦1 ¦ home user ¦
¦2 ¦ desktops ¦
¦3 ¦ laptop ¦
¦4 ¦ 512mb ¦
----------------

product_groups
----------------------------
¦id ¦ product_id ¦ group_id ¦
----------------------------
¦1 ¦ 1 ¦ 1 ¦
¦2 ¦ 1 ¦ 2 ¦
¦3 ¦ 1 ¦ 4 ¦
¦4 ¦ 2 ¦ 3 ¦
¦5 ¦ 2 ¦ 4 ¦
----------------------------

[souFrag]

Obviously your statement will never happen: AND groups.id = 2 AND groups.id = 4... Because you are saying i want the same row to be equal to 2 different values.

To make it work, try this:

SELECT p.name
FROM products p, groups g, product_groups pg
WHERE p.id = pg.product_id
AND pg.group_id = g.id
AND (g.id = 2 OR g.id = 4)
GROUP BY p.gid

You should also try searching google for INNER JOIN, OUTER JOIN, GROUP BY and HAVING statements. I spent many years working with databases without knowing these statements.... and they are REALLY useful :-)