CODEWITHSUNDEEP
phpmysqlsql
user1646428
user1646428

Reputation: 179

multiple matching conditions

I am attempting to filter results based on matching keywords. This part I have successfully been able to implement. However, there is a need to make this match some exclusively and others inclusively. For instance, if a user selected "large" and "shirt", the product will need to match those exclusively. But the user might want to see them in red, green, and blue (inclusively). How can I make that happen? Below is the basic concept, but the UNION does not appear to work:

SELECT id
FROM Filters
WHERE disabled = '0' AND filter IN ('large','shirt')
GROUP BY id 
HAVING COUNT(filter) = 2

UNION DISTINCT

SELECT id
FROM Filters
WHERE disabled = '0' AND filter IN ('red','green','blue') 
GROUP BY id 
HAVING COUNT(filter) > 1

Thanks!

Upvotes: 0

Views: 85

Answers (1)

GMB
GMB

Reputation: 222722

If I followed you correctly, you should be able to use an aggregated query with a HAVING BY clause that implements the logic using conditional aggregation.

Consider the below query:

SELECT id
FROM Filters
GROUP BY id
HAVING
    MAX(CASE WHEN filter = 'large' THEN 1 END) = 1
    AND MAX(CASE WHEN filter = 'shirt' THEN 1 END) = 1
    AND MAX(CASE WHEN filter IN ('red','green','blue') THEN 1 END) = 1

Details:

This aggregated condition ensures that this id has a record where filter = 'large':

MAX(CASE WHEN filter = 'large' THEN 1 END) = 1

This checks that this id has a record where filter = 'shirt':

    MAX(CASE WHEN filter = 'shirt' THEN 1 END) = 1

And this checks that at least one of the 3 colors is available:

MAX(CASE WHEN filter IN ('red','green','blue') THEN 1 END) = 1

You should be able to adapt the logic to your exact requirements if needed.

Upvotes: 1

Related Questions