Get result with matching all search queries

Question

I have three tables: comics, tags and comicTags.
The comics table ID is connected through a foreign key with the comicTags table while the tags table ID is also connected to the comicTags table through the tagID.

comics table
+----+
| ID |
+----+
|  1 |
|  2 |
+----+

comicTags table
+---------+-------+
| comicID | tagID |
+---------+-------+
|       1 |     1 |
|       2 |     1 |
|       2 |     2 |
+---------+-------+

tags table
+----+-------+
| ID | tag   |
+----+-------+
|  1 | tag1  |
|  2 | tag2  |
+----+-------+

What I'd like to achieve is, if I'm searching for tag1 AND tag2 I'd only like to get comic ID 2 as a result. Given is a string with the two tag names.

SELECT c.ID FROM `tags` `t`
    LEFT JOIN comicTags AS ct 
      ON ct.tagID=t.ID
    LEFT JOIN comics AS c
      ON c.ID=ct.comicID
    WHERE ((t.tag LIKE 'tag1')
      OR (t.tag LIKE 'tag2'))
    GROUP BY c.ID

With this statement I'm obviously getting comic ID 1 as well which I do not want. Changing from OR to AND doesn't work in the statement I've created.

Could you point me in the right direction on how to get only those comic IDs that match all tag IDs?

why you need the result be only ID=2 ? do you want max(ID) ? — Juan Carlos Oropeza, Jul 14 '15 at 15:13
Similar question that uses the `IN` clause instead of multiple `LIKE` parameters: http://stackoverflow.com/questions/11636061/matching-all-values-in-in-clause — AdamMc331, Jul 14 '15 at 15:20
Are you sure you mean to use `LIKE` here? You're not using any wildcards, so it is essentially the same as `t.tag = 'tag1'` — AdamMc331, Jul 14 '15 at 15:28

AdamMc331 · Accepted Answer · 2015-07-14T16:32:51.717

2

You need to search for rows where the tag_id is one of the tags you are looking for, and then ensure that the number of rows returned is equal to the number of tags, like this:

SELECT c.id
FROM comics c
JOIN comicTags ct ON ct.comicID = c.id
JOIN tags t ON t.id = ct.tagID AND t.tag IN ('tag1', 'tag2')
GROUP BY c.id
HAVING COUNT(*) = 2;

I have changed the condition to use the IN operator. Your current query does not have any wildcards, so I assume you are looking for tags that are an exact match, in which case this query is a little nicer.

Here is an SQL Fiddle example.

Note that if it's possible for a comic to be aligned with a tag more than once, you may want to change your query to COUNT(DISTINCT t.id) to ensure that the number of unique tags matches. Here is an SQL Fiddle example that shows repeated tags not being returned with the current query, but will be with the distinct added.

edited Jul 14 '15 at 16:32

answered Jul 14 '15 at 15:16

AdamMc331

16,492
10
71
133

1

or possibly count distinct – Strawberry Jul 14 '15 at 15:29
1

@Strawberry I hope I interpreted your suggestion for count distinct properly. – AdamMc331 Jul 14 '15 at 15:34
Obviously, LIKE is unnecessary here, and therefore IN() is so much more elegant. – Strawberry Jul 14 '15 at 15:34
@Strawberry I agree with the `LIKE` comment. I posted a comment underneath the question to ask OP for clarification, but since they used `LIKE` in their original query I decided not to change that without knowing for sure. – AdamMc331 Jul 14 '15 at 15:35
It would only matter if the OP was using a case sensitive collation. – Strawberry Jul 14 '15 at 15:37
@Strawberry understood. Out of curiosity, did I interpret your suggestion correctly? – AdamMc331 Jul 14 '15 at 16:31
1

I think you nailed it. – Strawberry Jul 14 '15 at 16:32
There's a first time for everything! – AdamMc331 Jul 14 '15 at 16:33
1

Even a stopped watch... ;-) – Strawberry Jul 14 '15 at 16:33

Barranka · Answer 2 · 2015-07-14T16:12:59.053

0

Maybe a subquery with ALL is what you need:

select c.id
from tags as t
     left join comicTags as ct on t.id = ct.tagId
     left join comics as c on c.id = ct.comicId
where t.id = ALL (select id from tags where t.tag in ('tag1','tag2'))

edited Jul 14 '15 at 16:12

answered Jul 14 '15 at 15:17

Barranka

20,547
13
65
83

1

I get a syntax error with this. Is there supposed to be a comparison operator before `ALL`? – AdamMc331 Jul 14 '15 at 15:35

score 0 · Answer 3 · answered Jul 14 '15 at 15:27

you can run one query per "tag" you are looking for and then join the result:

select c.*
from
(
    SELECT comicID 
    FROM tags t JOIN comicTags AS ct ON ct.tagID=t.ID
    WHERE (t.tag LIKE 'tag1')
) a join
(
    SELECT comicID 
    FROM tags t 
        JOIN comicTags AS ct ON ct.tagID=t.ID
    WHERE (t.tag LIKE 'tag2')
)b on a.comicID = b.comicID
join comics c on c.ID = a.comicID

score 0 · Answer 4 · answered Jul 14 '15 at 15:51

Check sqlFiddle

SELECT *
FROM comics c
WHERE 
    EXISTS  (SELECT comicID 
             FROM comicTags ct INNER JOIN tags t
                ON ct.tagID = t.ID
             WHERE 
                ct.comicID = c.ID
             AND t.tag = 'tag1')
AND EXISTS  (SELECT comicID 
             FROM comicTags ct INNER JOIN tags t
                ON ct.tagID = t.ID
             WHERE 
                ct.comicID = c.ID
             AND t.tag = 'tag2');

Get result with matching all search queries

4 Answers4