CODEWITHSUNDEEP
phpmysqlfiltersubquery
Bijan
Bijan

Reputation: 8586

MySQL WHERE filter based on subquery in SELECT

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.

Upvotes: 0

Views: 134

Answers (1)

forpas
forpas

Reputation: 164069

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

Upvotes: 1

Related Questions