up vote 4 down vote favorite

long time lurker here, been using the advice from this site for years, and now I have a question that I can't find an answer to - I'm sure the solution is simple though!

I'm setting up a tagging system for music tracks. I have three tables

Tracks

---------------
| id | name   |
---------------
| 1  | Track1 |
| 2  | Track2 |
---------------

Taglinks

--------------------------
| id | tag_id | track_id |
--------------------------
| 1  | 1      | 1        | 
| 2  | 2      | 1        | 
| 3  | 2      | 2        |
--------------------------

Tags

-------------------------
| id | tag      | type  |
-------------------------
| 1  | acoustic | genre | 
| 2  | anger    | mood  |
-------------------------

I want to be able to get the track name of tracks which have, for example, tag:acoustic, type:genre AND tag:anger, type:mood. So in this case I want track 1 as this has both tags.

The current query I have is

SELECT tracks.name 
FROM tracks
JOIN taglinks ON tracks.id=taglinks.track_id
JOIN tags ON (tags.type='genre' AND tags.tag='acoustic') AND (tags.type='mood' AND tags.tag='anger')
WHERE taglinks.tag_id=tags.id;

This returns nothing, if I remove the AND section in the second JOIN then I get all tracks tagged acoustic as expected and if I change it to an OR I get both tracks, again as expected.

How do I do an AND though and return only those tracks that have both those tag and type?

Thanks in advance :D

EDIT:

Just to clarify what I am after. I want to be able to retrieve tracks that have both Acoustic and Anger as tags, but only when those tags also match the given type.

This is because later I might have a tag called jazz with type genre, and a tag called jazz with type style and I need to be able to make sure I am selecting by the correct type of tag as well as the tag itself.

Here's the link to the sqlfiddle I'm working with - http://sqlfiddle.com/#!2/aad82/3.

Gilles's answer seems to be a good solution so far!

share|improve this question
@samsamX - I would upvote that edit if I could :) – webnoob Dec 13 '12 at 15:37
I am pleased with that edit, how did you layout the tables like that? – David Healey Dec 13 '12 at 15:45

3 Answers

up vote 1 down vote accepted

The problem is that the tracks should have the 2 tags equals to the asked values, all request posted yet will not work in that situation. The must should be to use the INTERSECT sql command but this one is not available in mysql.

Something like that should work :

SELECT tracks.id, tracks.name
FROM  `taglinks` 
LEFT JOIN tags ON tags.id = taglinks.tag_id
LEFT JOIN tracks ON tracks.id = taglinks.track_id
WHERE (
tag =  'accoustic'
AND `type` =  'genre'
)
OR (
tag =  'anger'
AND `type`=  'mood'
)
GROUP BY track_id
HAVING COUNT( * ) =2
  • I select all rows which match at least one tag
  • I count the number of tage that match with a GROUP BY.
  • If I search two parameters (here accoustic = 'genre' and anger = 'mood' and found 2 rows for this track then it matches the 2 conditions and I keep it to the final result
  • Just remember to change the HAVING COUNT( * ) =2 with the number of tag we want to search
share|improve this answer
This seems to do the trick, thanks! :D – David Healey Dec 14 '12 at 14:31
Oh actually this doesn't quite do it, I changed acoustic to foo and it still returns track 1 – David Healey Dec 14 '12 at 14:37
@DavidHealey - We are all coming up with a similar solution (although I don't know why people are grouping by anything) so I think the way you have asked your question is misleading. Can you clarify your question please? – webnoob Dec 14 '12 at 14:57
Ok, i will have a check – Gilles Hemmerlé Dec 14 '12 at 15:01
When I test by replacing by foo, I get 0 result as expected – Gilles Hemmerlé Dec 14 '12 at 15:04
show 1 more comment
up vote 2 down vote

This should be Ok

select
t.name
from
tracks as t 
inner join taglinks as tl on t.id = tl.track_id
inner join tags as tg on tl.tag_id = tg.id
where
tg.tag = 'acoustic' and
tg.type = 'genre' and 
tg.tag = 'anger' and
tg.type = 'mood';

Note: I using as identifier as I prefer it that way because it saves writing the table names everywhere. You don't have to.

EDIT

I may have misunderstood your question a little so this one might help as well:

select
t.name
from
tracks as t 
inner join taglinks as tl on t.id = tl.track_id
inner join tags as tg on tl.tag_id = tg.id
where
(tg.tag = 'acoustic' and
tg.type = 'genre') or 
(tg.tag = 'anger' and
tg.type = 'mood');

This will bring back results that have the correct type AND tag but don't have to have all of them.

share|improve this answer
Thanks for the prompt reply, this doesn't seem to do quite what I want but it is useful code none-the-less – David Healey Dec 14 '12 at 14:33
@DavidHealey - Can you provide an example of your data on sqlfiddle.com? I can adjust the query to suit then – webnoob Dec 14 '12 at 14:47
sqlfiddle.com/#!2/aad82/4 – David Healey Dec 14 '12 at 17:00
up vote 1 down vote 
SELECT * FROM tracks tracks
INNER JOIN
(SELECT taglinks.track_id, COUNT(*) as cnt
FROM tags tags
INNER JOIN taglinks taglinks ON taglinks.tag_id = tags.id
WHERE
    (tags.tag='ac' AND tags.type='ge')
    OR (tags.tag='an' AND tags.type='mo')
GROUP BY taglinks.track_id
)AS track_match
    ON track_match.track_id = tracks.id AND track_match.cnt = 2

You will need to replace "track_match.cnt = 2" 2=number of conditions to match

This solution gives some flexibility: you can also have tracks with a minimum number of conditions satisfied.

For example here you could add track_match.cnt >= 1 and also ORDER BY track_match.cnt DESC

to get a list of track ordered by the number of matched conditions.

share|improve this answer
Thanks, I haven't tried this one out yet as I came across Gilles's solution first, but I will give it a go and see how it compares :) – David Healey Dec 14 '12 at 14:32
Yours works too :D – David Healey Dec 14 '12 at 16:59

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

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