Mysql select data using multiple conditions

Question

Hi, I have a table like

id   fid
20    53
23    53
53    53

Here I need to return true when my condition is something like .. where fid=53 and id in(20,23,53) and also I need to return false when....where fid=53 and id in(20,24,53). But the above condition not satisfy my requirement.Both queries returns same value(true).Please help me to go forward.

Answer 1

maybe a subquery?

SELECT id, fid FROM table
WHERE fid = 53 AND
id IN ( SELECT id FROM table WHERE id IN(20,24,53))

Answer 2

I believe what you're asking for is for the query to find the fid that is associated with ALL values 20,23,53 and if not, then that fid isn't returned by the query.

There are two common solutions to this in SQL.

First the one that I recommend for MySQL:

SELECT t1.fid
FROM mytable t1
JOIN mytable t2 ON t1.fid = t2.fid
JOIN mytable t3 ON t1.fid = t2.fid
WHERE (t1.id, t2.id, t3.id) = (20,23,53);

Here's another solution that uses group by instead of self-joins, but tends to perform worse in MySQL:

SELECT t.fid
FROM mytable t
WHERE t.id IN (20,23,53)
GROUP BY t.fid
HAVING COUNT(*) = 3;

Answer 3

According to your example conditions, its not possible with a single query :(. since where fid=53 and id in(20,23,53) return true for fid=53 and id =20,53 Similarly, where fid=53 and id in(20,24,53) is false for fid=53 and id =20,53

the only difference is about id=23 and id=24. So you can do it with two difference query. But sometimes they will return true and false for same data.

I think you need to think about your question once again.