2

I have a database designed with tree tables:

checklist (id, name)
tag (id, name)
checklist_tag (checklist_id - FK, tag_id - FK)

Running this SQL:

SELECT cc.id as checklist_id, cc.name as checklist_name, ct.name as tag_name 
FROM checklist cc INNER JOIN checklist_tag cct ON cc.id = cct.checklistId 
INNER JOIN tag ct ON cct.tagId = ct.id 
WHERE ct.name IN ('tag 1', 'tag 2')

I get this result:

checklist_id    checklist_name  tag_name
1               checklist 1     tag 1
1               checklist 1     tag 2
3               checklist 3     tag 2
4               checklist 4     tag 2

But I need to select the checklists having the tags 1 and 2. Something like this:

checklist_id    checklist_name  tag_name
1               checklist 1     tag 1
1               checklist 1     tag 2

Is there anyway to do that with a single select?

1

Use a subquery that has the checklist id that has both tags

SELECT
    B.id checklist_id,
    B.name checklist_name,
    D.name tag_name
FROM
(
    SELECT AA.checklistId,COUNT(1) tagcount
    FROM checklist_tag AA
    INNER JOIN tag BB ON AA.tagId = BB.id
    WHERE BB.name IN ('tag 1', 'tag 2')
    GROUP BY AA.checklistId HAVING COUNT(1)=2
) A
INNER JOIN checklist B     ON A.checklistId = B.id
INNER JOIN checklist_tag C ON A.checklistId = C.checklist_id
INNER JOIN tag D           ON C.tag_id = D.id;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.