MySQL Query using joins and several ANDs does not work well

This is my query:

SELECT dt1.*, document.*
FROM documenttags dt1
LEFT JOIN document ON dt1.id = document.id
WHERE
  (dt1.tag = 'agent' AND dt1.value = 'excel' )
AND
  (dt1.tag = 'extension' AND dt1.value = 'xls' )

The query will work fine up until the AND after the first tag and value pair:

SELECT dt1.*, document.*
FROM documenttags dt1
LEFT JOIN document ON dt1.id = document.id
WHERE (dt1.tag = 'agent' AND dt1.value = 'excel' )

A typical result of the second query is :

id | tag | value | ...rest of columns'
'11 | agent | excel | ... rest of columns

And finally, a search for id=11 yields:

id | tag | value |
11 | agent | excel |
11 | extension| xls|

So I'm really puzzled why my original query doesn't work?


You probably meant this from the beginning but I didn't get it.

What you want (as I think now), is to correlate a (row from table) document to 2 or more (rows from) documenttags. This can be achieved with 2 or more JOINs from table documents to documenttags, as below.

If you have 3 criteria, you'll need 3 JOINs, etc.

The following query will give you all documents that have both excel tagged as agent, and xls tagged as extension:

(Sidenote: yes, you were right, "AND" is the correct term to use here!)

SELECT dt1.*
     , dt2.*
     , document.*
FROM document
  JOIN documenttags dt1
    ON dt1.id = document.id
  JOIN documenttags dt2
    ON dt2.id = document.id
WHERE
  (dt1.tag = 'agent' AND dt1.value = 'excel' )
  AND
  (dt2.tag = 'extension' AND dt2.value = 'xls' )

This would work too. With one JOIN and a grouping:

SELECT document.*
     , COUNT(document.id) AS filtersPassed
FROM document
  JOIN documenttags dt
    ON dt.id = document.id
      --filters (conditions) go here:
WHERE (dt.tag = 'agent' AND dt.value = 'excel')
   OR (dt.tag = 'extension' AND dt.value = 'xls')
GROUP BY document.id
HAVING filtersPassed = 2             --number of filters

Using OR here because the query works like this:

For every document, it checks all related rows and keeps those according to your conditions. We can't use AND here as every row is checked one by one and of course a row cannot have two tags, just one. So, we keep all good tags and then we group them together and count how many there are for every document (grouping is the usual way to count rows in SQL.) Then we just have to keep documents that passed both filters (= 2).

Second query has the advantage that can be used for a more complex query, if for example you have 4 filters and you want to find documents that pass 3 or 4 of those filters. Just put the 4 conditions (with ORs) and filtersPassed >= 3.