CODEWITHSUNDEEP
mysql
user3025605
user3025605

Reputation: 323

MySQL query to fetch all products and its category

I am trying to fetch products and include the associated categories as a comma separated list. Below is what I tried, but it returns only 1 product. I tried LEFT and LEFT OUTER joins but neither worked. Any ideas?

SELECT 'U' as ACTION
       , product_id
       , GROUP_CONCAT(PC.category_id SEPARATOR ', ') as Categories 
FROM   Products P LEFT JOIN Product_Category PC 
           ON PC.product_id = P.product_id

Upvotes: 0

Views: 184

Answers (2)

piotrgajow
piotrgajow

Reputation: 2950

GROUP_CONCAT function is one aggregate functions, and is using GROUP BY clause. If you have no GROUP BY in your SELECT statement, then it uses all found results.

Upvotes: 1

Mahesh Madushanka
Mahesh Madushanka

Reputation: 2998

add group by 

SELECT 'U' as ACTION, product_id, GROUP_CONCAT(PC.category_id SEPARATOR ', ')     as Categories 
FROM Products P LEFT JOIN Product_Category PC 
ON PC.product_id = P.product_id group by P.product_id;

-----------------example -----------

select * from calls;
+----+------------+---------+
| id | date       | user_id |
+----+------------+---------+
|  1 | 2016-06-22 |       1 |
|  2 | 2016-06-22 |    NULL |
|  3 | 2016-06-22 |    NULL |
|  4 | 2016-06-23 |       2 |
|  5 | 2016-06-23 |       1 |
|  6 | 2016-06-23 |       1 |
|  7 | 2016-06-23 |    NULL |
+----+------------+---------+
7 rows in set (0.03 sec)

mysql> select * from payments;
+----+------------+---------+-------+
| id | date       | user_id | value |
+----+------------+---------+-------+
|  1 | 2016-06-22 |       1 |    10 |
|  2 | 2016-06-22 |       3 |    15 |
|  3 | 2016-06-22 |       4 |    20 |
|  4 | 2016-06-23 |       2 |   100 |
|  5 | 2016-06-23 |       1 |   150 |
+----+------------+---------+-------+
5 rows in set (0.00 sec)

mysql> select c.user_id,group_concat(p.value) from calls c inner join payments p on p.user_id=c.user_id group by c.user_id;
+---------+-----------------------+
| user_id | group_concat(p.value) |
+---------+-----------------------+
|       1 | 10,150,10,150,10,150  |
|       2 | 100                   |
+---------+-----------------------+
2 rows in set (0.00 sec)

Upvotes: 1

Related Questions