How to condition SQL WHERE

Question

I would like to condition when to GROUP_CONCAT my results. I can't figure out the way how to separate my 'category' and 'condition' results.

SELECT d.company_name AS 'organisation name',
       GROUP_CONCAT(DISTINCT tag.tag) AS 'category',
       GROUP_CONCAT(DISTINCT tag.tag) AS 'conditions', d.contact_describtion AS 'description', d.tel, d.tel2, d.mobile, d.email, d.email2, d.web, d.web2, d.address, d.town, d.county, d.post_code AS 'post code', IF(d.state = 1, "published", "unpublished") AS 'status',d.contact_page_notes AS 'contact history', d.last_contacted_date AS 'last contacted', d.last_updated_date AS 'last updated'
FROM jds4a_directory d INNER JOIN
     jds4a_tags_resources res
     on d.id = res.resource_id INNER JOIN
     jds4a_tags tag
     on tag.id = res.tag_id
WHERE tag.category = 'condition' OR tag.category = 'category'
GROUP BY res.resource_id

What I want to achieve is to only display category tags under the category and only display condition tags under conditions

Answer 1

Use case. You are also returning way too many columns, I think:

SELECT d.company_name AS organisation_name,
       GROUP_CONCAT(DISTINCT (CASE WHEN tag.category = 'condition' THEN tag.tag END)) AS categories,
       GROUP_CONCAT(DISTINCT (CASE WHEN tag.category = 'category' THEN tag.tag END)) AS conditions
FROM jds4a_directory d INNER JOIN
     jds4a_tags_resources res
     on d.id = res.resource_id INNER JOIN
     jds4a_tags tag
     on tag.id = res.tag_id
WHERE tag.category IN ('condition', 'category')
GROUP BY d.company_name;

Notes:

• The SELECT columns should be consistent with the GROUP BY columns. Don't overload with extraneous columns.
• GROUP BY should match the unaggregated keys/expressions in the SELECT.
• Use IN instead of a bunch of ORs -- easier to maintain, write, and less prone to error.
• Use single quotes for string and date constants. Don't use them for column aliases.