Forum Moderators: open
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 ¦
----------------------------
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 :-)
