Select multiple entries in SQLite where multiple rows need to match the same condition (including AND, OR and NOT)

Question

I have a question regarding selecting in SQLite. I have given entries in a DB, where i need to select all rows which match my criterion. These criterion can be combined with AND, OR or NOT.

For example, I have

     id  |  image  |  tag
    -----|---------|------
     21  |     21  |   35
     25  |    240  |   45
     36  |     21  |   40
     43  |    155  |   35
     56  |    243  |   35
     65  |    312  |   40

as data in my DB.

What I want to do now, is to search for example for all entries with the tag 35. So far so easy (simple SELECT query). This gives me the images 21, 155 and 243.

1. But now, I want every entry with the tag 35, BUT ONLY the ones, that have 40 as tag as well. This would be a AND constrain.

   • This would result in only 21

2. Besides from an AND constrain, I want also to search for entries, which have a tag of 35, BUT ALSO for the ones with a tag of 40 (an OR constrain).

   • This should result in the images 21, 155, 243 and 312

3. Furthermore, I want to exclude some entries with a NOT constrain. For example, I want every entry with a tag 35, BUT NOT the ones with a tag 40.

   • The result of this would be the images 155 and 243

How can I achieve this with SQLite-queries? I know, how to search for multiple criterion inside an entry, but I have at this time no idea, how to search over multiple entries with this constrains. I could solve this by selecting two different lists, and then intersect, union or except them with each other. But I think, there must be a better, direct way.

I hope, this goal can be reached, without writing horribly long queries which contain thousands of sql-statements. ;)

Does someone know, how to search for entries like described above?

Thank you in advance for your help

Kind regards Ramon

(Please keep some of my English mistakes; there are free of charge ;))

Answer 1

Using compound queries is the simplest solution:

SELECT image FROM data WHERE tag = 35
INTERSECT
SELECT image FROM data WHERE tag = 40;

SELECT image FROM data WHERE tag = 35
UNION
SELECT image FROM data WHERE tag = 40;

SELECT image FROM data WHERE tag = 35
EXCEPT
SELECT image FROM data WHERE tag = 40;

Using correlated subqueries would also be possible:

1. All entries with tag 35 for which a row with the same image and the tag 40 exists:

   SELECT *
   FROM data
   WHERE tag = 35
     AND EXISTS (SELECT *
                 FROM data AS d2
                 WHERE d2.image = data.image
                   AND d2.tag = 40);
   

2. Nothing complicated:

   SELECT DISTINCT image
   FROM data
   WHERE tag IN (35, 40);
   

3. All entries with tag 35 for which no row with the same image and the tag 40 exists:

   SELECT *
   FROM data
   WHERE tag = 35
     AND NOT EXISTS (SELECT *
                     FROM data AS d2
                     WHERE d2.image = data.image
                       AND d2.tag = 40);