SQL Query over three different tables

Question

i got three tables

CATS
id           name
------------------------------
1            category1
2            category2
3            category3
4            category4


PRODUCT
id           name
------------------------------
1            product1
2            product2



ZW-CAT-PRODUCT
id_cats      id_product
------------------------------
1            1
3            1
4            2

now i want to get my products and their categories

product1 => category1,category3
product2 => category4

is there a way to get this array (or object or something) with one mysql query? i tried a bit with JOINS, but it seems thats this is not exactly what i need, or?

currently i'm using 3 querys (i think thats too much).

any suggestions?

edit

and on the other way, what if i want to get ALL products of a specific category? can this also be done in one query?

Answer 1

If you need just comma-separated list of categories for every product, look at MySQL's GROUP_CONCAT() aggregate function:

SELECT p.*, GROUP_CONCAT(c.name) AS categories
FROM PRODUCT p
LEFT JOIN ZW-CAT-PRODUCT cp ON p.id = cp.id_product
LEFT JOIN CATS c ON cp.id_cats = c.id
GROUP BY p.id

To get all products of a specific category (by category ID):

SELECT p.*
FROM PRODUCT p
INNER JOIN ZW-CAT-PRODUCT cp ON p.id = cp.id_product
WHERE cp.id_cats = 42

The same, but by category name:

SELECT p.*
FROM PRODUCT p
INNER JOIN ZW-CAT-PRODUCT cp ON p.id = cp.id_product
INNER JOIN CATS c ON cp.id_cats = c.id
WHERE c.name = 'category1'

Answer 2

You could fit all 3 into 1 query yes, but consider having 3 huges tables, i'd rather process them one by one instead of getting the whole bulk back in 1 time. This takes longer, but is (in my opinion) more data friendly.

Answer 3

You can use GROUP_CONCAT to get a separated list in your results.

SELECT p.*, 
       GROUP_CONCAT(c.name SEPARATOR ',') as cats
FROM PRODUCT p
LEFT JOIN ZW-CAT-PRODUCT l
  ON l.id_product=p.id
LEFT JOIN CATS c
  ON c.id=l.id_cats
GROUP BY p.id

So basically, this first does some joins to get all the data. If you were to replace the GROUP_CONCAT line with just c.name, you would see a row for each product_id/category pair. The GROUP BY tells it to group results based on product ID, and then GROUP_CONCAT(c.name..) is telling to it take all the different c.name values that occur in a group (so for each product ID, since you're grouping by product ID) and concatenate those values into one string, using , as the separator.

So to get all products for a each category in the same style, it would be like this,

SELECT c.*, 
       GROUP_CONCAT(p.name SEPARATOR ',') as products
FROM CATS c
LEFT JOIN ZW-CAT-PRODUCT l
  ON l.id_cats=c.id
LEFT JOIN PRODUCT p
  ON p.id=l.id_product
GROUP BY c.id

EDIT: To get just the product rows for a particular category (as requested in comment), it's this.

SELECT p.*
FROM PRODUCT p
LEFT JOIN ZW-CAT-PRODUCT l
  ON l.id_product=p.id
LEFT JOIN CATS c
  ON c.id=l.id_cats
WHERE c.name='xyz';