Problem with using AND, OR for single column values

Example Mysql tableI 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.

6 (+1) visits

One thought to “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;

Leave a Reply

Your email address will not be published. Required fields are marked *