MySQL在单个资源ID上加入3个表

I have three tables

我有三张桌子

• resources_connection
  1. resource_id
  2. resource_tag_id

resources_connection resource_id resource_tag_id

• resources_flags
  1. user_id
  2. resource_id

resources_flags user_id resource_id

• resources_votes
  1. user_id
  2. resource_id

resources_votes user_id resource_id

Each is a 2 column table, int (11) for both designed to allow me to query the number of tags, flags and votes based on a single 'resource id'

每个都是一个2列表,int(11),两者都旨在允许我根据单个'资源id'查询标签,标志和投票的数量

I am currently using this query to try and get a count of tags (resources_connection), flags(resources_flags) and votes(resources_votes):

我目前正在使用此查询来尝试获取标记(resources_connection),标记(resources_flags)和投票(resources_votes)的计数:

SELECT COUNT(DISTINCT t1.resource_id) as votes,
        COUNT(DISTINCT t2.resource_id) as flags,
        COUNT(DISTINCT t3.resource_tag_id) as tags
FROM ecruit_demo.resources_votes t1
LEFT JOIN ecruit_demo.resources_flags t2
    ON (t1.resource_id = t2.resource_id)
JOIN ecruit_demo.resources_connection t3
    ON (t1.resource_id = t3.resource_id) WHERE t1.resource_id = 4

The problem is that this query returns proper results for resource_id = 1 but when I set resource_id to 4 (for which there is a tag) it returns all zeros. What would be the proper query structure to ensure that this query always returns the proper count of tags, flags and votes for a given resource_id?

问题是此查询返回resource_id = 1的正确结果,但是当我将resource_id设置为4(有标记)时,它返回全零。什么是正确的查询结构,以确保此查询始终返回给定resource_id的标记,标志和投票的正确计数?

I should also add that the only place resource_id = 4 occurs in the database is in resources_connection, the other two tables do not have this value

我还应该补充一点,资源中出现的唯一地方是resource_id = 4,在resources_connection中,其他两个表没有这个值

2 个解决方案

#1

SELECT  resource_ID,
        MAX(CASE WHEN types = 'votes' THEN totals ELSE NULL END) votes,
        MAX(CASE WHEN types = 'flags' THEN totals ELSE NULL END) flags,
        MAX(CASE WHEN types = 'tags' THEN totals ELSE NULL END) tags
FROM    
    (
        SELECT  resource_ID, 'votes' types, 
                COUNT(DISTINCT resource_ID) totals
        FROM    resources_votes
        GROUP   BY resource_ID
        UNION
        SELECT  resource_ID, 'flags' types, 
                COUNT(DISTINCT resource_ID) totals
        FROM    resources_flags
        GROUP   BY resource_ID
        UNION
        SELECT  resource_ID, 'tags' types, 
                COUNT(DISTINCT resource_tag_id) totals
        FROM    resources_connection
        GROUP   BY resource_ID
    ) s
-- WHERE    resource_ID = 1
GROUP   BY resource_ID

#2

I'm not really good in mysql but maybe you can try:

我在mysql中不是很好,但也许你可以尝试:

change JOIN to LEFT JOIN so the query would be like this.

将JOIN更改为LEFT JOIN,以便查询将如下所示。

 SELECT COUNT(DISTINCT t1.resource_id) as votes,
            COUNT(DISTINCT t2.resource_id) as flags,
            COUNT(DISTINCT t3.resource_tag_id) as tags
    FROM ecruit_demo.resources_votes t1
    LEFT JOIN ecruit_demo.resources_flags t2
        ON (t1.resource_id = t2.resource_id)
    LEFT JOIN ecruit_demo.resources_connection t3
        ON (t1.resource_id = t3.resource_id) WHERE t1.resource_id = 4