Lukáš Rutar Lukáš Rutar - 6 months ago 10
SQL Question

MySQL group by "referencing" query

suppose i have this table

stuff_property
:

| stuff_id (fk) | property_id |
| ------------- | ----------- |


now i want to make query that would return stuff and its properties like this:

SELECT stuff_id
, GROUP_CONCAT(DISTINCT property_id) as property_list
FROM stuff_property
GROUP
BY stuff_id


but i want to list only stuff that has certain property, lets say 42. i cannot add simply
WHERE property_id = 42
because then grouping would "fail" and
GROUP_CONCAT
wound not list all properties, only '42'. Best I can do is to use nested queries, but it is terribly inefficient:

SELECT a.stuff_id, GROUP_CONCAT(DISTINCT a.property_id) as property_list
FROM stuff_property a GROUP BY a.stuff_id
WHERE 42 IN
(SELECT b.property_id FROM stuff_property b WHERE b.stuff_id = a.stuff_id)


I would like to know how to do some backward group referencing, so i could make query effective as this (made up pseudo code):


SELECT stuff_id, GROUP_CONCAT(DISTINCT property_id) as property_list
FROM stuff_property GROUP BY stuff_id WHERE 42 IN
grouping-reference.property_id



Is there a way to do it?

Answer

There are three ways to do this:

using the subquery as you have (considered to be the most inefficient)

using a Join (better, but still inefficient)

SELECT stuff_id, GROUP_CONCAT(DISTINCT property_id) as property_list
  FROM stuff_property 
  INNER JOIN (SELECT stuff_ID FROM stuff_property where property_ID = 42) SubSet
 GROUP BY stuff_id

Use exists keyword (Generally considered most efficient)

SELECT sp.Stuff_ID, GROUP_CONCAT(DISTINCT sp.property_id) as property_list
FROM stuff_property sp
  WHERE EXISTS (SELECT 1 FROM stuff_property s
                WHERE s.stuff_ID = sp.stuff_Id
                and s.property_ID=42);

in both the exists and the join you're in essence saying find a set of stuff that has a property ID. now return the all properties for the stuff in that subset.

As far as proving efficiency. This is based on several articles on line and my own practical experience. But don't take my or their word for it. review execution plans for all three of these to see what's beset for you!

These next two MAY work...

This uses find_in_set which parses a string seperated by commas for a value

SELECT stuff_id, GROUP_CONCAT(DISTINCT property_id) as property_list
  FROM stuff_property 
WHERE find_in_set('42',GROUP_CONCAT(DISTINCT property_id)) > 0

This might work too, but again performance starts to get to be more problematic..

Select * from (
Select stuff_ID, Group_Concat(Distinct property_ID) as Property_list      
FROM stuff_property) B
WHERE find_in_set('42',Property_List) > 0