MySQL Query using joins and multiple AND's not quite working

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开发者_JS百科 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?

As far as I can tell,
- dt1.tag can't be both agent and extension just like
- dt1.value can't be both excel and xls.

Consider changing your AND to an OR.

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

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.

I think, if possible, that you should consider changing your schema.

Why not adding agent and extension fields directly to your document table (or related specific tables, such as for example document_agent and document_extension if a document can have several agents and extensions) ?