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
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 | 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
The following query will give you all documents that have both
excel tagged as
xls tagged as
(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
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 (
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
filtersPassed >= 3.