CODEWITHSUNDEEP
mysql
HKumar
HKumar

Reputation: 1624

mysql group_concat with where in condition

I have three table product

ID name
1  A
2  B
3  C

vendor

ID Name
1  V1
2  V2
3  V3

product_vendor

ID pid vid
1  1   1  
2  1   2
3  2   3
4  3   1
5  3   2
6  3   3

i want left join and create response like below table

p_id  product  vendors
1     A        V1,V2
2     B        V3
3     C        V1,V2,V3

below is the my solution but it's quite complicated. Is it possible to have a short query for this

SELECT GROUP_CONCAT(v2.`name`) AS vendername,
       p.*,
       GROUP_CONCAT(v.`v_id`) AS vi
FROM `products` p
JOIN `product_vendor` v ON p.id = v.p_id,
                           `vendors` v2
WHERE v2.id IN
    (SELECT GROUP_CONCAT(p.`id`) AS vi
     FROM `vendors` p
     JOIN `product_vendor` v ON p.id = v.p_id
     GROUP BY p.id)
GROUP BY p.id
ORDER BY `p_id` ASC

Upvotes: 1

Views: 69

Answers (2)

Sinto
Sinto

Reputation: 3997

Can you try this, I do not have executed the query but thinks it will help:

SELECT `P`.`id`, `P`.`name`, CONCAT(GROUP_CONCAT(CAST(`V`.`name` AS CHAR)), ", ") as vendername 
FROM `product` P 
JOIN `product_vendor` PV ON (`PV`.`pid` = `P`.`id`) 
JOIN `vendor` V ON (`V`.`ID` = `PV`.`vid`) 
GROUP BY `PV`.`pid`

For listing all records in Product table use:

SELECT `P`.`id`, `P`.`name`, CONCAT(GROUP_CONCAT(CAST(`V`.`name` AS CHAR)), ", ") as vendername 
FROM `product` P 
LEFT JOIN `product_vendor` PV ON (`PV`.`pid` = `P`.`id`) 
LEFT JOIN `vendor` V ON (`V`.`ID` = `PV`.`vid`) 
GROUP BY `PV`.`pid`
ORDER BY `P`.`id`

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

Just do it with joins and group_concat no need for sub queries

SELECT 
    p.id, 
    p.name, 
    Group_concat(v.name) AS vendername 
FROM
    product p 
JOIN 
    product_vendor pv ON p.id = pv.pid 
JOIN 
    vendor v ON pv.vid = v.id 
GROUP BY 
    p.id, p.name 
ORDER BY 
    p.id ASC

Demo

Upvotes: 1

Related Questions