dongzhanjuan5141 2014-04-21 13:37
浏览 104
已采纳

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

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

展开全部

  • 写回答

1条回答 默认 最新

  • douluan8828 2014-04-21 14:11
    关注

    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.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
编辑
预览

报告相同问题?

悬赏问题

  • ¥15 PADS Logic 原理图
  • ¥15 PADS Logic 图标
  • ¥15 电脑和power bi环境都是英文如何将日期层次结构转换成英文
  • ¥20 气象站点数据求取中~
  • ¥15 如何获取APP内弹出的网址链接
  • ¥15 wifi 图标不见了 不知道怎么办 上不了网 变成小地球了
手机看
程序员都在用的中文IT技术交流社区

程序员都在用的中文IT技术交流社区

专业的中文 IT 技术社区,与千万技术人共成长

专业的中文 IT 技术社区,与千万技术人共成长

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

客服 返回
顶部