1

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?

1
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.