MySQL query to select items in a linked table based on AND

Question

I have a table tblPhotos of photo details:

| photoID | photoName |
| ------- | --------- |
| 1       | w         |
| 2       | x         |
| 3       | y         |
| 4       | z         |

and another table tblPhotoTags of tags to photos:

| photoID | tagID |
| ------- | ----- |
| 1       | 1     |
| 1       | 2     |
| 2       | 1     |
| 3       | 2     |
| 4       | 1     |
| 4       | 2     |

I am trying make a couple of queries that will pick out the photos that have any given tags, either AND or OR. In the example let's say I am searching for the photos linked to tagID 1 AND/OR 2.

OR should pick out all of the photos (1, 2, 3 and 4).

AND should only pick out 1 and 4.

I have the following for OR which works fine:

SELECT DISTINCT tblPhotos.photoID FROM tblPhotos 
    INNER JOIN tblPhotoTags ON tblPhotos.photoID = tblPhotoTags.photoID 
    WHERE tblPhotoTags.tagID = 1 OR tblPhotoTags.tagID = 2

But I am struggling to work out how to do the AND query.

Answer 1

If you need only the ids of the photos, then there is no need to join to tblPhotos.

For the 1st case (OR), use DISTINCT and just a WHERE clause:

SELECT DISTINCT photoID
FROM tblPhotoTags
WHERE tagID IN (1, 2);

For the 2nd case (AND) use aggregation and set the condition in the HAVING clause:

SELECT photoID
FROM tblPhotoTags
WHERE tagID IN (1, 2)
GROUP BY photoID
HAVING COUNT(*) = 2 -- the number of tagIDs in the IN list

If you also want the name of the photos then join to tblPhotos:

SELECT DISTINCT p.*
FROM tblPhotos p INNER JOIN tblPhotoTags t
ON t.photoID = p.photoID
WHERE t.tagID IN (1, 2);

and:

SELECT p.photoID, p.photoName
FROM tblPhotos p INNER JOIN tblPhotoTags t
ON t.photoID = p.photoID
WHERE t.tagID IN (1, 2)
GROUP BY p.photoID, p.photoName
HAVING COUNT(*) = 2 -- the number of tagIDs in the IN list

See the demo.