MySQL WHERE filter based on subquery in SELECT

Question

I have a table named Records that shows products. I also have a table named Categories that shows the categories for each individual product (if one exists).

The Categories table is structured liked:

id    category_id
--    -----------
1     1
1     3
3     1
3     2
5     4

The query I run to pull record ID and category ID(s) is:

SELECT 
    Records.id,
    (SELECT
        GROUP_CONCAT(C.category_id)
    FROM `Categories` C
    WHERE Records.id = C.id) AS 'CategoryName'
FROM 
    Records

The output will return:

id    CategoryName
--    ------------
1     1,3
2     NULL
3     1,2
4     4
5     NULL

I have an area of my website where users can filter records by category. Let's say user wants to filter for category = 1 or 2. I was thinking I just tack on a WHERE FIND_IN_SET(1,CategoryName) OR FIND_IN_SET(2,CategoryName) but this does not work because of the MySQL execution order and CategoryName column does not exist yet.

What is the best way to filter for category_id? The input for categories will be comma separated but I can use PHP to explode() the string to separate them.

forpas · Accepted Answer · 2021-08-12T22:06:14.800

1

You can rewrite the query with a LEFT join of Records to Categories:

SELECT r.id,
       GROUP_CONCAT(c.category_id) AS CategoryName
FROM Records r LEFT JOIN Categories c
ON c.id = r.id
GROUP BY r.id

and if you want to use the same query for filtering all you have to do is add at the end a HAVING clause:

HAVING FIND_IN_SET(1, CategoryName) OR FIND_IN_SET(2, CategoryName)

Or, you can filter first and then aggregate:

SELECT r.id,
       GROUP_CONCAT(c.category_id) AS CategoryName
FROM Records r INNER JOIN Categories c
ON c.id = r.id
WHERE c.category_id IN (1, 2)
GROUP BY r.id

edited Aug 12 '21 at 22:06

answered Aug 12 '21 at 21:59

forpas

160,666
10
38
76

It would not be an `INNER JOIN` since there will not always be a match for category. But using `LEFT JOIN` worked perfectly. – Bijan Aug 12 '21 at 22:49
@Bijan the 2nf query should be an INNER join because it is used only for filtering (even with LEFT join it would actually be an INNER join because of the WHERE clause). The 1st query, with a LEFT join, can be used without the HAVING clause to get all the rows and for filtering with the HAVING clause. – forpas Aug 13 '21 at 06:42

MySQL WHERE filter based on subquery in SELECT

1 Answers1