67028


3つのtableがあります。

[code]

    product
    id | name
    1  | hoge
    2  | fuga
    
[/code]

* * *
[code]

    tag
    id | name
    11 | tag1
    22 | tag2
    
[/code]

* * *
[code]

    map_product_tag
    product_id | tag_id
    1          | 11
    1          | 22
    2          | 22
    
[/code]

* * *

期待する結果

[code]

    product_id | tags
    1          | [{11,tag1},{22,tag2}]
    2          | [{22,tag2}]
    
[/code]

* * *

sql

[code]

    select 
      product.id as proruct_id, 
      product.name as product_name, 
      tag.id as tag_id,
      tag.name as tag_name
    from product
    left join 
      map_product_tag 
    on 
      product.id = map_product_tag.product_id
    left join tag on map_product_tag.tag_id = mst_tag.id;
    
[/code]

この場合結果は

[code]

    +--------------------------------------+--------------+--------------------------------------+------------------+
    | proruct_id                           | product_name | tag_id                               | tag_name         |
    +--------------------------------------+--------------+--------------------------------------+------------------+
    | 1                                    | hoge         | 11                                   | tag1             |
    | 1                                    | fuga         | 22                                   | tag2             |
    +--------------------------------------+--------------+--------------------------------------+------------------+
    
[/code]

となります。  
product_idが同じ場合、tag_idを"11,22"のように1行で取得したいです.  
※ [{tag_id, tag_name}]のようにできることが一番望ましいです。


CC BY-SA 4.0
2020-05-27T01:53:08.420
67020
2020-05-27T07:03:01.540
2020-05-27T04:12:42.410
40347
40347
question
mysql sql
SQL N x NのjoinにてList in ListのQueryをしたい


ご希望の書式と完全に一致しないかもしれませんが、JSON_OBJECTAGGがヒントになるかもしれません。

[code]

    select
       product_id,
       JSON_OBJECTAGG(tag_id, tag_name) as tags
    from
       (
          select
             product.id as product_id,
             product.name as product_name,
             tag.id as tag_id,
             tag.name as tag_name 
          from
             product 
             left join
                map_product_tag 
                on product.id = map_product_tag.product_id 
             left join
                tag 
                on map_product_tag.tag_id = tag.id 
       )
       as x 
    group by
       product_id
    
[/code]

実行結果

[code]

    product_id  tags
    1   {"11": "tag1", "22": "tag2"}
    2   {"22": "tag2"}
    
[/code]

次のようにJSON_ARRAYAGGを使う方が希望の書式に近いかもしれません。

[code]

       JSON_ARRAYAGG(JSON_ARRAY(tag_id, tag_name) )  as tags
    
[/code]

実行結果

[code]

    product_id  tags
    1   [[11, "tag1"], [22, "tag2"]]
    2   [[22, "tag2"]]
    
[/code]


CC BY-SA 4.0
2020-05-27T06:56:44.313
67028
2020-05-27T07:03:01.540
2020-05-27T07:03:01.540
7837
7837
67020
answer