Multiple Attributes from one Query

Question

I am having a problem getting multiple attributes for one item. Below is my Table:

Table: product_attrib

id | product_id | name | value 
------------------------------
 0 |         33 | age  |   25
 1 |         33 | size |   25

My problem is when I join the query, I only get one of the attributes with such a query:

Query:

SELECT 
    p.*
    ,pa.name
    ,pa.value
FROM product AS p
LEFT OUTER JOIN product_attrib AS pa ON (
    p.id = pa.product_id
)

My Results

"products_id":"0",
"products_price":"0.0000",
"products_name":null,
"products_description":null,
"attrib_name":"color",
"attrib_value":"red"

Do you see how I only get one attribute set? Is there a way I can get all the attributes for a product?

Answer 1

Most likely, your original query is right as it is. You probably want the product, no matter if attributes can be found.

You can reverse the order of the tables in the JOIN to prevent losing rows from product_attrib like this (if product with product_id 33 does not exist):

SELECT
     p.*
    ,pa.name
    ,pa.value
FROM product_attrib AS pa 
LEFT JOIN product AS p ON p.id = pa.product_id

But that's probably not what you want.

A LEFT [OUTER] JOIN includes all rows from the left hand table and adds values from the right table where the JOIN condition can be fulfilled (potentially creating multiple rows if multiple matches are found in the right hand table.) If no matching row can be found in the right hand table NULL values are substituted for all columns of the right hand table.
Start by reading the manual here.

If you want "all attributes" per product in the same row, you need to aggregate values. Something like this:

SELECT p.*
      ,group_concat(pa.name) AS att_names
      ,group_concat(pa.value) AS att_values
FROM  product AS p
LEFT  JOIN product_attrib AS pa ON p.id = pa.product_id
WHERE p.product_id = 33
GROUP BY p.*;