使用UNION的SQL语句中的限制WHERE

Question

I have the following tables:

• cards
• uploads
• tags
• tag_upload: Relation between a tag and an upload.
• card_tag: Relation between a tag and a card.

Card 1 has a relation to the tags "event", "rmc", and "patient".

Upload 1 has a relation to the tags "event" and "patient".

Upload 2 has a relation to the tags "event", "rmc", and "patient".

Query 1:

I've selected the tags "event" and "patient".

It should return Upload 1, Upload 2, and Card 1.

Query 2:

I've selected the tags "event", "rmc", and "patient".

It should return Upload 1 and Card 1.

The Problem:

I'm currently using the following but when I do this Query 2 returns the same as Query 1:

What would the query look like that would select a card if it had the tags in it? Could I see an example or logic flow you might use?

This is my first version trying to get what I want down. Will be turned into testable code right after with PHPSpec.

Solution

Thank you eggyal!

SELECT *
FROM(
    SELECT   cards.id, cards.name, cards.type,
             cards.updated_at, cards.created_at, cards.image
    FROM     cards
        JOIN card_tag ON card_tag.card_id = cards.id
        JOIN tags     ON card_tag.tag_id  =  tags.id
    WHERE    NOT tags.deleted
         AND cards.type NOT IN ('','libraries')
         AND cards.account_user_id = 1
         AND tags.name IN ('rmc', 'test')
    GROUP BY cards.id
    HAVING   COUNT(DISTINCT tags.id) = 1
UNION ALL
    SELECT   uploads.id, uploads.name, uploads.type,
             uploads.updated_at, uploads.created_at, uploads.image
    FROM     uploads
        JOIN tag_upload ON tag_upload.upload_id = uploads.id
        JOIN tags     ON tag_upload.tag_id  =  tags.id
    WHERE    NOT tags.deleted
         AND uploads.type NOT IN ('','libraries')
         AND uploads.account_user_id = 1
         AND tags.name IN ('rmc','test')
    GROUP BY uploads.id
    HAVING   COUNT(DISTINCT tags.id) = 2
) AS qry
    ORDER BY `updated_at` DESC
    LIMIT 0, 35

Answer 1

As a starting point, consider joining your tables, grouping and filtering the results:

SELECT   cards.id, cards.name, cards.type,
         cards.updated_at, cards.created_at, cards.image
FROM     cards
    JOIN card_tag ON card_tag.card_id = cards.id
    JOIN tags     ON card_tag.tag_id  =  tags.id
WHERE    NOT deleted
     AND cards.type NOT IN ('','libraries')
     AND cards.account_user_id = ?
     AND tags.name IN ('event','patient')
GROUP BY cards.id
HAVING   COUNT(DISTINCT tags.id) = 2

If (card_id, tag_id) is guaranteed to be unique in card_tag, one can replace COUNT(DISTINCT tags.id) with the more performant COUNT(*).

One can then UNION with a similar query on the uploads table.