CODEWITHSUNDEEP
mysqlsql
jukerok
jukerok

Reputation: 357

one to many returns only one result

I have have 3 tables

table1: product_id name description

table2: category_id name description

table3: product_id category_id

Im trying to create a query that selects a table with the following data: product_name,product_description,product_category

The thing is each row should contain more than one product_category but the query only returns one.

Here's the query, if you could kindly let me know what part is incorrect

SELECT table1.name AS product_name,
       table1.description AS product_description,
       table2.name AS product_category
FROM table1 INNER JOIN
     table3
     ON table3.product_id = table1.product_id INNER JOIN
     table2
     ON table2.category_id = table3.category_id;

The result i get is : 

product_name  -   product_description  -  product_category
NAME 1    -      Description 1     -     Category 1
NAME 2    -      Description 2     -     Category 2

Im aiming for something like this: 

product_name  -   product_description  -  product_category
NAME 1    -      Description 1         -     Category 1
                                             Category 2
                                             Category 5

NAME 2    -      Description 2     -     Category 7
                                         Category 2
                                         Category 1

Upvotes: 0

Views: 51

Answers (2)

jukerok
jukerok

Reputation: 357

I have found the answer all credit goes to @Gordon Linoff as all i needed to do was to add the product_id to the grouping

Here's the query:

SELECT t1.name AS product_name,
t1.description AS product_description,
GROUP_CONCAT(t2.name) AS product_category
FROM table1 t1 INNER JOIN
table3 t3
ON t3.product_id = t1.product_id INNER JOIN
table2 t2
ON t2.category_id = t3.category_id
GROUP BY t1.product_id, t1.name, t1.description;

Thanks!

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

If you want multiple product categories in one row, then think "aggregation":

SELECT t1.name AS product_name,
       t1.description AS product_description,
       GROUP_CONCAT(t2.name) AS product_category
FROM table1 t1 INNER JOIN
     table3 t3
     ON t3.product_id = t1.product_id INNER JOIN
     table2 t2
     ON t2.category_id = t3.category_id
GROUP BY t1.name, t1.description;

Upvotes: 1

Related Questions