A complex SQL Query using group_concat

Question

I have a table called 'stock':

    stock_id | prod_id | size | color | shop_id | qty

    1        |   4     |   L  |  Red  |    1    |  3
    2        |   4     |   XL |  Blue |    2    |  1
    3        |   4     |   L  |  Red  |    3    |  2
    4        |   2     |  XXL | White |    1    |  7
    5        |   4     |   L  |  Red  |    4    |  1

Some of that..

I have a products table and a shop table.

What I want as result is something like that:

    prod_id | prod_name | shop_name |  color_size_qty
       4    |  T-Shirt  |  Shop 1   |    Red,L,(3)
       2    |  T-Shirt  |  Shop 1   |    White,XXL,(7)
       4    |  T-Shirt  |  Shop 2   |    Blue,XL,(1)

I dont know if you can understand me. The closest I got was something like

prod id | prod_name |        Colors         |     sizes   |
   4    | T-Shirt   |Red,Red,Red,Blue,White | L,L,L,XL,XL

Not even close as you can see. Hope you can help me.

SELECT p.*, GROUP_CONCAT(DISTINCT e.estoque_prod_cor ORDER BY e.estoque_prod_cor) as cores, GROUP_CONCAT(DISTINCT e.estoque_prod_tam ORDER BY e.estoque_prod_tam) as tamanhos from produtos p, estoque e WHERE p.produto_id = e.produtos_produto_id GROUP BY p.produto_nome — Paulo Barros
– Paulo Barros, Commented Jul 16, 2012 at 11:15

newfurniturey · Accepted Answer · 2012-07-13 19:44:59Z

5

A simple CONCAT() may be all you need as opposed to a GROUP_CONCAT():

SELECT
    prod_id, prod_name, shop_name,
    CONCAT(color, ',', size, ', (', qty, ')') AS color_size_qty
FROM
    stock
    JOIN products ON stock.prod_id=products.id
    JOIN shops ON stock.shop_id=shops.id
ORDER BY
    prod_name ASC, shop_name ASC

answered Jul 13, 2012 at 19:44

newfurniturey

38.6k10 gold badges99 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

DaveRandom Over a year ago

Looks good, although as it is you get Column 'prod_id' in field list is ambiguous. Just need to disambiguate it and it works like a charm though - working example.

DaveRandom Over a year ago

@PauloBarros Sorry, so you can get a result like what?

Paulo Barros Over a year ago

@DaveRandom thank you man. Very much. I don't mean to bother you too much, but how can I alter your query so I can get a result like that:--------------------------------------------------------------- prod_name | shop_name total_prods_per_shop[] | COlor_size_qty(All in one single line) like: --------------------------------------------- T-shirt | shop1 Total(9) | Black,XL(3) / Red,L(2) / White,S(4)------T-shirt | shop2 Total(3) | Blue,XL(1) / Yellow,L(2) .... and so on. Thank you again!

DaveRandom Over a year ago

@PauloBarros Oh right I see what you mean, like this?

Paulo Barros Over a year ago

@DaveRandom dude! you're awesome! Thank you! Now I see how to use CONCAT and GROUP_CONCAT, is so much clear to me now. Thank you again.

|

Collectives™ on Stack Overflow

A complex SQL Query using group_concat

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related