Problem with using AND, OR for single column values

mysql table Problem with using AND, OR for single column valuesI 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.

One thought on “Problem with using AND, OR for single column values

  1. You may use sub queries after where condition such as :

    SELECT *, count(*) as total_count FROM td1,dt2 WHERE (td1.id1=td2.id1 and td1.id1=1 and td2.id1=(select * from td2 where td2.id1=2 or td2.id1=3) GROUP BY t1.id2;