mysql 6表在一个查询中联接在一起

Question

我有6个表:用户、日记、文件、标志、flag_details

users (id, avatar, fullname)
journals (id, container, container_id, user_id, title)
files (id, container, container_id, user_id, title)
flags (id, container, container_id, total)
flag_details (id, container, container_id, reason, comment, user_id)
trend_item (id, container, container_id, user_id)

关系是：

if(flags.container='look') then flags.container_id = journals.id

所以我从日志表中获取所有记录

if(flags.container='photo') then flags.container_id = files.id

所以我从文件表中获取所有记录

if(flags.container='trend') then flags.container_id = trend_item.id

所以我从trend_item表中获取所有记录

所以我的观点是：

id | container | things      | total flag
1  | look      | 123 - guy   | 2
2  | photo     | 321 - budi  | 4
3  | trend     | 345 - elvis | 3
4  | look      | 876 - cans  | 6

所以我得到了这样的查询：

SELECT DISTINCT (id), created_at, container, IF( container =  'look', (

SELECT a.container_id
FROM flags a, journals b
WHERE a.container_id = b.id
), IF( container =  'photo', (

SELECT a.container_id
FROM flags a, files b
WHERE a.container_id = b.id
), (

SELECT a.container_id
FROM flags a, trend_items b
WHERE a.container_id = b.id
) ) ) , total, 
`status`
FROM flags
ORDER BY created_at DESC 

但答案是子查询返回超过1行。

如果将所有这些放在一个查询中，有可能吗？

Answer 1

我认为您可以使用一个查询安全地连接所有这些表，如下所示：

    SELECT fl.*
      FROM flags fl
 LEFT JOIN journals jo
        ON fl.container_id = jo.id AND fl.container = 'look'
 LEFT JOIN files fi
        ON fl.container_id = fi.id AND fl.container = 'photo'
       ...

这里有一些a demo的例子来说明这个概念。

Answer 2

SELECT t6.username AS username, MAX( t1.earn_status ) AS genx, MAX( t2.earn_status ) AS grnplus, MAX( t3.earn_status ) AS genpower, MAX( t4.earn_status ) AS genpro, MAX( t5.earn_status ) AS geextra 
FROM genx_commission t1 
    LEFT JOIN gen_plus_commission t2 ON ( t1.user_id = t2.user_id ) 
    LEFT JOIN gen_power_commission t3 ON ( t1.user_id = t3.user_id ) 
    LEFT JOIN gen_pro_commission t4 ON ( t1.user_id = t4.user_id ) 
    LEFT JOIN gen_extra_commission t5 ON ( t1.user_id = t5.user_id ) 
    LEFT JOIN users t6 ON ( t1.user_id = t6.id ) 
WHERE t1.user_id =1654