CODEWITHSUNDEEP
mysql
edam
edam

Reputation: 958

MySQL select query ANDed multiple condition of same column

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.

Upvotes: 0

Views: 1461

Answers (1)

JuniorCompressor
JuniorCompressor

Reputation: 20005

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.

Upvotes: 1

Related Questions