MySQL Select many columns when using GROUP_CONCAT

Question

I have been given this great answer on how to select distinct rows from 3 of my database tables:

select p.p_id, p.`p_name`, p.brand, GROUP_CONCAT(DISTINCT c.c_id SEPARATOR ', ') as categories, GROUP_CONCAT(DISTINCT s.s_id SEPARATOR ', ') as shops
from product p inner join product_category c on p.p_id = c.p_id
               inner join product_shop s on p.p_id = s.p_id
where c.c_id in (2,8)
  and s.s_id in (1,2)
group by p.p_id, p.`p_name`, p.brand

I would just like to expand on that to now also select more columns from shop and category tables. I want to add in s.s_name and c.c_name to the select results. How would I do this? I am trying with CONCAT from this answer eg:

GROUP_CONCAT(
  DISTINCT CONCAT(tags.id,',',tags.displayName) 
  ORDER BY posts.id 
  SEPARATOR ';'
)

So adding that in to my query to just add the category.c_name like this:

select p.p_id, p.`p_name`, p.brand, GROUP_CONCAT(DISTINCT CONCAT(category.c_id, category.c_name) SEPARATOR ', ') as categories, GROUP_CONCAT(DISTINCT s.s_id SEPARATOR ', ') as shops
from product p inner join product_category c on p.p_id = c.p_id
               inner join product_shop s on p.p_id = s.p_id
where c.c_id in (2,8)
  and s.s_id in (1,2)
group by p.p_id, p.`p_name`, p.brand

But for some reason that is giving this error:

1054 - Unknown column 'category.c_id' in 'field list'

How do I correctly add in more columns from category and shop tables to my select query?

`category` table is not in the join clause did you mean `product_category` ? and if so then `CONCAT(c.c_id, c.c_name)` should do the job. — Abhik Chakraborty, May 04 '16 at 07:13
If the table ain't in the query then it's columns ain't there neither — Strawberry, May 04 '16 at 07:15
I see an alias for `categories`, not `category`. Did you try replacing `category` with `categories`? — starshine531, May 04 '16 at 07:16
Incidentally (or perhaps not), what do you do with this query after? — Strawberry, May 04 '16 at 07:18
@Strawberry I can now see that I need to include the category and the shop tables in the sql query. I will be displaying products and their categories and the shops that have them. — BeniaminoBaggins, May 04 '16 at 07:33

BeniaminoBaggins · Answer 1 · 2016-05-04T08:23:33.027

I had to add the shop and category tables into the query with inner join. The full sql statement is:

SELECT p.p_id, p.`p_name`, p.brand, 
GROUP_CONCAT(DISTINCT CONCAT(c.c_id,':',c.c_name) SEPARATOR ', ') as categories, 
GROUP_CONCAT(DISTINCT CONCAT(s.s_id,':',s.s_name) SEPARATOR ', ') as shops
FROM product p 
INNER JOIN product_category pc on p.p_id = pc.p_id
INNER JOIN category c ON c.c_id = pc.c_id
INNER JOIN product_shop ps on p.p_id = ps.p_id
INNER JOIN shop s ON s.s_id = ps.s_id
WHERE c.c_id in (2,8)
AND s.s_id in (1,2)
GROUP BY p.p_id, p.`p_name`, p.brand

MySQL Select many columns when using GROUP_CONCAT

1054 - Unknown column 'category.c_id' in 'field list'

1 Answers1