PHP MYSQL query, searching multiple fields/columns using data from multiple tables

Question

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!

Answer 1

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

Answer 2

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.

Answer 3

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.