MySQL Query for selecting values based on 2 columns

Question

I have a table with 4 columns: id, A, B, C. I need to select A based on B and C.

B is a filter and has integer values, C are the filter options, also integer.

I tried this:

SELECT a FROM table WHERE (b=1 AND c IN (5,6,7)) AND (b=2 AND c IN (9,10,11))

But that doesn't work because B can't be 1 and 2 at the same time. I tried with OR:

SELECT a FROM table WHERE (b=1 AND c IN (5,6,7)) OR (b=2 AND c IN (9,10,11))

This returns A but wrong... I need both conditions to be correct.

How can I make this query work?

Thank you!

There are a lot of conditions (over 10 filters (B) and over 100 options (C)). But every filter has only max. 10 options. The conditions are numbers/ids from another table but I pass them to the query with some HTML checkboxes. — doubleb
– doubleb, Commented Aug 5, 2015 at 9:51
The second query looks good to me. Both conditions can never be correct simultaneously/ — Strawberry
– Strawberry, Commented Aug 5, 2015 at 10:07
Yes they can be, 'A' can have multiple filters at the same time. — doubleb
– doubleb, Commented Aug 5, 2015 at 10:11

juergen d · Accepted Answer · 2015-08-05 10:22:32Z

1

Group by a and then you can check the conditions in the having clause

SELECT a 
FROM your_table 
GROUP BY a
HAVING sum(b=1 AND c IN (5,6,7)) > 0
AND sum(b=2 AND c IN (9,10,11)) > 0

edited Aug 5, 2015 at 10:22

answered Aug 5, 2015 at 9:47

juergen d

205k40 gold badges303 silver badges376 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

doubleb Over a year ago

This solution works only if I have 2 filters selected, but if I add more (3,4 etc.) it doesn't work. I tried to add a var with the total number of active filters instead of the "2" at the end but it doesn't work.

juergen d Over a year ago

Updated the answer. This works without any variable.

simbabque · Accepted Answer · 2015-08-05 10:30:47Z

0

SELECT a 
    FROM your_table 
WHERE 
    CASE 
        WHEN b=1 THEN C IN(5,6,7) 
        WHEN b=2 THEN C IN(8,9,10)
    END

edited Aug 5, 2015 at 10:30

simbabque

54.4k8 gold badges77 silver badges141 bronze badges

answered Aug 5, 2015 at 10:04

Chintan7027

7,6498 gold badges40 silver badges51 bronze badges

1 Comment

doubleb Over a year ago

I get the same results as when I used "OR" between the conditions. If A1 has both conditions correct and A2 has only the last one both show up, I only need A1 to show (the ones that respect all the conditions).

Collectives™ on Stack Overflow

MySQL Query for selecting values based on 2 columns

2 Answers 2

2 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Related