0

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.

  • My table schema above is very simplyfied - table 'photos' contain some more data like 'user_id'. In the output I need 'id' and 'user_id' - two columns form table 'photos' – Valery Jul 22 '15 at 11:34
2

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.

1

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 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.