MYSQL Select, where rows exists

Question

I have a Mysql Database and tables with the structure:

1. Table 'photos' - ID, Name
2. Table 'photos_tags' - Key, Photo_ID, Tag

My goal is to select all photos, which has one tag (it's not a problem) and more tags at once.

I've tried this query, but it's not work

SELECT photos.id FROM photos, photos_tags WHERE photos.id = photos_tags.photo_id AND (tag = 'cute' OR tag = 'cool')

The main problem - this query will select both photos with one of these tags or with two of them. But i need only photos with two these tags at one photo.

Can you help me please? I'm new at MySQL

UPDATE: May be there is a better way to organize this schema? My current realization has 'tags' text-field right in table 'photos'. And I use FIND_IN_SET to make a select. But my table has about 1.5M rows - and some queries are very slow.

Answer 1

This is a "relational division" problem and there are several ways to solve.

One way is with two EXISTS subqueries:

SELECT p.id 
FROM photos AS p
WHERE EXISTS
      ( SELECT 1
        FROM photos_tags AS pt
        WHERE p.id = pt.photo_id 
          AND pt.tag = 'cute'
      )
 AND  EXISTS
      ( SELECT 1
        FROM photos_tags AS pt
        WHERE p.id = pt.photo_id 
          AND pt.tag = 'cool'
      ) ;

Another with two joins (assuming that the photo_tags table has a unique constraint on (photo_id, tag_id), i.e. no photo can been tagged with the same tag twice):

SELECT p.id 
FROM photos AS p
  JOIN photos_tags AS pt1
    ON  p.id = pt1.photo_id 
    AND pt1.tag = 'cute'
  JOIN photos_tags AS pt2
    ON  p.id = pt2.photo_id 
    AND pt2.tag = 'cool' ;

One more way is @Abhik's answer, with one join and GROUP BY.

You can see a few more ways (more than 13) in this awesome answer: How to filter SQL results in a has-many-through relation. Not all of them will work in MySQL as the question is for Postgres but the problem is identical.

Strictly speaking - and if there is a FOREIGN KEY from photo_tags to photos - the photos table can be removed from all the above queries. You can return the photo_id instead. But I guess you may want more columns from the photo table in the result set.

Answer 2

The easiest way would be group by and having something as

select
p.id
from photos p
join  photos_tags pt on pt.photo_id = p.id
where pt.tag in ('cute','cool')
group by p.id
having count(*) = 2 ;