I have a MySql database table as shown in image . Where table has thousands of records with any integer and varchar values in id1 and id2.

I want to select values where id1=(1 and (2 or 3)). Which will result in id2=a,b,c.

Result may be several hundred records for real table and I will be using LIMIT start, records for showing results on each page.

• For selecting records id1= 1 and 2 and 3:
  SELECT *, count(*) as total_count FROM table WHERE id1=1 OR id1=2 OR id1=3 GROUP BY id2 HAVING l_count=’3′;
• For selecting records id1= 1 or 2 or 3:
  SELECT *, count(*) as total_count FROM table WHERE id1=1 OR id1=2 OR id1=3 GROUP BY id2;

But how to select mixed and , or statements, like id1=(1 and (2 or 3))?
I found some solution.

To select id1=(1 AND (2 OR 3))

SELECT t1.* FROM test t1 INNER JOIN test t2 USING (id2) WHERE t1.id1=1 AND (t2.id1=2 OR t2.id1=3) GROUP BY t1.id2;

Which requires using JOIN for every AND condition in the query. For example to select id1=(1 AND 2 AND 3 AND (4 OR 5 OR 6)). Query sting will be as following.

SELECT t1.* FROM test t1 INNER JOIN test t2 USING (id2)
INNER JOIN test t3 USING (id2)
INNER JOIN test t4 USING (id2)
WHERE t1.id1=1 AND t2.id1=2 AND t3.id1=3 AND (t4.id1=4 OR t4.id1=5 OR t4.id1=6) GROUP BY t1.id2;

Which will get bigger and bigger id there are more AND conditions required.
I think that there must be more optimal way doing it. If you have any suggestion or solution you are wellcome to comment on here.

I came up with this problem when someone wants to list all images having tags ((“North London” or “East London” or “City”) and “street” and “birds” and “flowers” and “people”) . Where each image can have several tags and each tag may have thousands images.

Related terms:

• count column value in mysql