CODEWITHSUNDEEP
mysql
Harold
Harold

Reputation: 699

Mysql SELECT IN GROUP_CONCAT

I want to use IN a subquery in mysql but I need GROUP_CONCAT to have a having (my sub query work well).

SELECT pd.* FROM Product pd 
WHERE perimeter_id IN (SELECT GROUP_CONCAT(p.id) 
FROM Perimeter p 
GROUP BY p.network_id, p.productRange_id, p.country_id 
HAVING count(id) > 1);

Upvotes: 0

Views: 856

Answers (2)

Rahul
Rahul

Reputation: 77934

I think you can do a INNER JOIN like

SELECT pd.* 
FROM Product pd 
INNER JOIN Perimeter p ON pd.perimeter_id = p.id
GROUP BY p.network_id, p.productRange_id, p.country_id 
HAVING count(DISTINCT p.id) > 1;

Upvotes: 0

fthiella
fthiella

Reputation: 49089

You don't have to use GROUP_CONCAT. This query will return all (network_id, productRange_id, country_id) that have multiple IDs:

SELECT p.network_id, p.productRange_id, p.country_id, GROUP_CONCAT(p.id) 
FROM Perimeter p 
GROUP BY p.network_id, p.productRange_id, p.country_id 
HAVING count(id) > 1

This query will return all IDs in different rows:

SELECT p.ID
FROM
  Perimeter p INNER JOIN (
    SELECT p.network_id, p.productRange_id, p.country_id, GROUP_CONCAT(p.id) 
    FROM Perimeter p 
    GROUP BY p.network_id, p.productRange_id, p.country_id 
    HAVING count(id) > 1
  ) d ON (p.network_id, p.productRange_id, p.country_id) = (d.network_id, d.productRange_id, d.country_id)

and this is your final query:

SELECT pd.*
FROM Product pd 
WHERE
  perimeter_id IN (
SELECT p.ID
FROM
  Perimeter p INNER JOIN (
    SELECT p.network_id, p.productRange_id, p.country_id, GROUP_CONCAT(p.id) 
    FROM Perimeter p 
    GROUP BY p.network_id, p.productRange_id, p.country_id 
    HAVING count(id) > 1
  ) d ON (p.network_id, p.productRange_id, p.country_id) =
         (d.network_id, d.productRange_id, d.country_id)
)

Upvotes: 1

Related Questions