MySQL select query ANDed multiple condition of same column

Question

I have the below test table with the following rows in the table.

table: test_set(id (pk), pid int, fc int, fl int)

pid      fc    fl
1        7     30
1        8     31
1        9     35
2        7     39
2        8     40

Now if I run

SELECT pid FROM test_set WHERE fl=30 OR fl=35 GROUP BY pid;
#Result :
pid
---
1

As expected but if I want to run

SELECT pid FROM test_set WHERE fl=30 AND fl=35 GROUP BY pid;
#Result :
result set (0) # Nothing matched!

This is too as expected but I want to go beyond expectation here. My logic here is that fl=30 and fl=35 both have pid=1 in common i.e. when they are intersected they yeilds pid=1

So to be specific I need the result of multiple values of fl column that have one or more pid in common.

I have already read this this and commented there too.

possible duplicate of SQL Selecting From One Table With Multiple Where Clauses — Brian Driscoll
– Brian Driscoll, Commented Feb 17, 2015 at 18:09
@Brian I have read that. But what if there are more fl in condition? How many self join then? And in real application I can't rewrite join queries time and again. It's normally pre-written (of course by me) in business logic as a reusable query method.(You know that, right ?) — edam
– edam, Commented Feb 17, 2015 at 18:24
Did you try the other answer (using HAVING clause instead of self join)? — rlanvin
– rlanvin, Commented Feb 17, 2015 at 18:36
@rlanvin No sir I did not. I am looking for some normalized solution. For example using set methods (as in maths) I want the WHERE condition to be normalized. I don't want to solve it in a rigid way. Please never mind in my dogmatism. — edam
– edam, Commented Feb 17, 2015 at 19:00

JuniorCompressor · Accepted Answer · 2015-02-17 18:40:09Z

1

What about:

SELECT pid, COUNT(DISTINCT fl) AS count, GROUP_CONCAT(DISTINCT fl) list
FROM test_set
GROUP BY pid HAVING count > 1;

?

Output:

+------+-----------+------------------+
| pid  | count     | list             |
+------+-----------+------------------+
|    1 |         3 | 30,31,35         |
|    2 |         2 | 39,40            |
+------+-----------+------------------+

Given two fl values:

SELECT pid, COUNT(DISTINCT fl) AS count
FROM test_set
WHERE fl IN (30, 35)
GROUP BY pid HAVING count = 2;

Output:

+------+-------+
| pid  | count |
+------+-------+
|    1 |     2 |
+------+-------+

Given three fl values:

SELECT pid, COUNT(DISTINCT fl) AS count
FROM test_set
WHERE fl IN (30, 31, 35)
GROUP BY pid HAVING count = 3;

Output:

+------+-------+
| pid  | count |
+------+-------+
|    1 |     3 |
+------+-------+

It's good to have indexes on pid and fl.

edited Feb 17, 2015 at 18:40

answered Feb 17, 2015 at 18:04

JuniorCompressor

20.1k4 gold badges33 silver badges58 bronze badges

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

2 Comments

edam Over a year ago

It should give pid =1 when fl=30 and fl=35 are ANDed? Can you please make that query out?

edam Over a year ago

What if there are more fl ? How come the self join work then? I mean how many times do I write self join?

Collectives™ on Stack Overflow

MySQL select query ANDed multiple condition of same column

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related