MySQL – Using GROUP_CONCAT Without Limit by WHERE Clause

group-concatenationMySQL

Buildings and Facilities have a many-to-many relation with a linking table of ids in between. I would like to select Buildings with specific Facilities, and also show each Building's other Facilities in a single column.

Using GROUP_CONCAT works if I SELECT based on TABLE Buildings,

SELECT `b`.`building_name`, `b`.`address`, 
GROUP_CONCAT(`ft`.`facility_type` ORDER BY `facility_type`) AS `facilities`
FROM `buildings` `b`
JOIN `buildings_2_facilities` USING (`building_id`)
JOIN `facility_types` `ft` USING (`facility_id`)
WHERE `b`.`latitude` > 1
GROUP BY `building_id`

http://sqlfiddle.com/#!9/ceec20/10

but if I also SELECT based on Facilities, only that Facility is returned in the GROUP_CONCAT.

SELECT `b`.`building_name`, `b`.`address`, 
GROUP_CONCAT(`ft`.`facility_type` ORDER BY `facility_type`) AS `facilities`
FROM `buildings` `b`
JOIN `buildings_2_facilities` USING (`building_id`)
JOIN `facility_types` `ft` USING (`facility_id`)
WHERE `b`.`latitude` > 1 AND `facility_id` = 2
GROUP BY `building_id`

http://sqlfiddle.com/#!9/ceec20/11

How can I return results including all of each Building's facilities?

Best Answer

SELECT `b`.`building_name`, `b`.`address`, 
GROUP_CONCAT(`ft`.`facility_type` ORDER BY `facility_type`) AS `facilities`,
GROUP_CONCAT(CASE `facility_id` WHEN 2 THEN `ft`.`facility_type` END ORDER BY `facility_type`) AS `facilities2`
FROM `buildings` `b`
JOIN `buildings_2_facilities` USING (`building_id`)
JOIN `facility_types` `ft` USING (`facility_id`)
WHERE `b`.`latitude` > 1
GROUP BY `building_id`;

http://sqlfiddle.com/#!9/ceec20/13

do you know a way to get rid of the rows with NULL for facilities2 in the third query?

We cannot refer to alias from output list in WHERE clause. To remove records with facilities2 IS NULL we must use appropriate condition in HAVING clause:

SELECT `b`.`building_name`, `b`.`address`, 
GROUP_CONCAT(`ft`.`facility_type` ORDER BY `facility_type`) AS `facilities`,
GROUP_CONCAT(CASE `facility_id` WHEN 2 THEN `ft`.`facility_type` END) AS `facility2`
FROM `buildings` `b`
JOIN `buildings_2_facilities` USING (`building_id`)
JOIN `facility_types` `ft` USING (`facility_id`)
WHERE `b`.`latitude` > 1 
GROUP BY `building_id`
HAVING `facility2` IS NOT NULL;

http://sqlfiddle.com/#!9/ceec20/17

UPDATE 2012-02-21 17:06 EDT

SELECT e.*, MAX(m.datetime) AS unread_last, COUNT(m.id) AS unread 
FROM
TAB_EVENT e LEFT JOIN
(
    SELECT * FROM
    (SELECT id,event_id,datetime FROM TAB_MESSAGE
    WHERE read IS NULL OR read = 0
    ORDER BY datetime DESC) mm
    LIMIT 10
) m
ON e.id=m.event_id
ORDER BY m.datetime DESC, e.id ASC;

@Sebastian, I put the query back in the original join order. Please try this as well !!!

UPDATE 2012-02-21 17:11 EDT

Make sure the datetime field is indexed

ALTER TABLE TAB_MESSAGE ADD INDEX read_datetime_ndx (read,datetime);

Mysql – How to GROUP_CONCAT DISTINCT values in a MySQL query that gets number of records and min/max values

UPDATE Now I see your error. The inner query uses aggregation, and takes out sizeRange which is not a column you aggregate on. So you only get "samples" of that column. Strictly speaking, your query is not valid SQL but MySQL allows it given a relaxed sql_mode.

So your query is inherently erroneous. Will see if I can help fix it.

ORIGINAL answer

My guess for you would be to check the value of group_concat_max_len.

It is by default just 1024, though you typically don't really want a limit for that.

The problem might be that you are only getting partial results, where, by chance or by order of evaluation, "S" and "XL" occupy first 1024 characters or more. I see no reason why "M" or "L" would not be there -- the GROUP_CONCAT doesn't do such distinctions.

So, try out:

SET group_concat_max_len := 1000000;

And execute your query again. If this works, make sure to set said param in you MySQL configuration file.

You may find my related post useful.

Best Answer

Related Solutions

Mysql – Limit WHERE to MAX() & COUNT()

UPDATE 2012-02-21 17:06 EDT

UPDATE 2012-02-21 17:11 EDT

Mysql – How to GROUP_CONCAT DISTINCT values in a MySQL query that gets number of records and min/max values

Related Question