I have a Mysql Database and tables with the structure:
- Table 'photos' – ID, Name
- 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.
Best Answer
This is a "relational division" problem and there are several ways to solve.
One way is with two
EXISTS
subqueries: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):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
fromphoto_tags
tophotos
- thephotos
table can be removed from all the above queries. You can return thephoto_id
instead. But I guess you may want more columns from thephoto
table in the result set.