组合 MySQL 中的多个查询结果（按列）

Combine Multiple Query Results in MySQL (by column)

提问by Imrul

I have 4 different queries and each of them return individual unique set of Results. I need to combine the Query Results with using a single query.

我有 4 个不同的查询，每个查询都返回一组独特的结果。我需要将查询结果与使用单个查询结合起来。

my sample queries are:

我的示例查询是：

1. select cls.* from (calls as cls inner join calls_users as clsusr on cls.id=clsusr.call_id) inner join users as usr on usr.id=cls.assigned_user_id where cls.assigned_user_id='seed_max_id'

2. select mtn.* from (meetings as mtn inner join meetings_users as mtnusr on mtn.id=mtnusr.meeting_id) inner join users as usr on usr.id=mtn.assigned_user_id where mtn.assigned_user_id='seed_max_id'

3. select tsk.* from tasks as tsk inner join users as usr on usr.id=tsk.assigned_user_id where tsk.assigned_user_id='seed_max_id'

4. select nts.* from (notes as nts inner join accounts as acnts on acnts.id=nts.parent_id) inner join users as usr on usr.id=acnts.assigned_user_id where acnts.assigned_user_id='seed_max_id'

I tried the following way, but it didn't work

我尝试了以下方法，但没有用

Combine: SELECT tbl1.*, tbl2.* 
from (select cls.* from (calls as cls inner join calls_users as clsusr on cls.id=clsusr.call_id) inner join users as usr on usr.id=cls.assigned_user_id where cls.assigned_user_id='seed_max_id') as tbl1 
left  outer join
(select mtn.* from (meetings as mtn inner join meetings_users as mtnusr on mtn.id=mtnusr.meeting_id) inner join users as usr on usr.id=mtn.assigned_user_id where mtn.assigned_user_id='seed_max_id') as tbl2
using(assigned_user_id)

i also tried right outer join and other inner joins I am really stuck, if any one know the solution then please help. I need the similar result like How can I join two tables with different number of rows in MySQL?.

我也尝试过正确的外连接和其他内连接我真的被卡住了，如果有人知道解决方案，请帮忙。我需要类似的结果，例如如何在 MySQL 中加入具有不同行数的两个表？.

Data Sample:

数据样本：

From Query 1:

来自查询 1：

+-------------------------------------------+------------------+-
| Call Name                                 | Call Description |
+-------------------------------------------+------------------+-
| Discuss Review Process                    | NULL             |
| Get More information on the proposed deal | NULL             |
| Left a message                            | NULL             |
| Discuss Review Process                    | NULL             |
+-------------------------------------------+------------------+

From Query 2:

来自查询 2：

+-----------------------+-----------------------------------------------------------
| Meeting Name          | Meeting Description
+-----------------------+-----------------------------------------------------------
| Review needs          | Meeting to discuss project plan and hash out the details o
| Initial discussion    | Meeting to discuss project plan and hash out the details o
| Demo                  | Meeting to discuss project plan and hash out the details o
| Discuss pricing       | Meeting to discuss project plan and hash out the details o
| Review needs          | Meeting to discuss project plan and hash out the details o
+-----------------------+-----------------------------------------------------------

i need to combine the columns like the following:

我需要组合如下列：

+-------------------------------------------+------------------+-------------------+-------------------+
| Call Name                                 | Call Description |Meeting Name       |Meeting Description|
+-------------------------------------------+------------------+-------------------+-------------------+
| Discuss Review Process                    | NULL             |Review needs       |Meeting to discuss |
| Get More information on the proposed deal | NULL             |Initial discussion |Meeting to discuss |
| Left a message                            | NULL             |Demo               |Meeting to discuss |
| NULL                                   | NULL             |Discuss pricing    |Meeting to discuss |
| NULL                                      | NULL             |Review needs       |Meeting to discuss |
+-------------------------------------------+------------------+-------------------+-------------------+

采纳答案by cletus

The best you can do is a UNION or UNION ALL but this requires them to have the same type and number of columns. For example:

您能做的最好的是 UNION 或 UNION ALL 但这要求它们具有相同的类型和列数。例如：

SELECT 'Customer' AS type, id, name FROM customer
UNION ALL
SELECT 'Supplier', id, name FROM supplier
UNION ALL
SELECT 'Employee', id, full_name FROM employee

The column names don't have to match. The aliases from the first part will be used for the rest.

列名不必匹配。第一部分的别名将用于其余部分。

I'll also add that instead of:

我还将添加它而不是：

select cls.* from (calls as cls inner join calls_users as clsusr on cls.id=clsusr.call_id) inner join users as usr on usr.id=cls.assigned_user_id where cls.assigned_user_id='seed_max_id'

you should remove the unnecessary subquery and just do:

您应该删除不必要的子查询，然后执行以下操作：

SELECT c.*
FROM calls c
JOIN calls_users cu ONc.id = cu.call_id
WHERE c.assigned_user_id = 'seed_max_id'

There's no need for the extra complexity and the above is eminently more readable.

不需要额外的复杂性，上面的内容明显更具可读性。

回答by egrunin

I assume you want your example to return a single row combining the corresponding entries from all these tables. Try this and tell us if it worked:

我假设您希望您的示例返回一行，将所有这些表中的相应条目组合在一起。试试这个并告诉我们它是否有效：

select * from users as usr
left outer join (calls as cls 
    inner join calls_users as clsusr 
    on cls.id = clsusr.call_id) 
on usr.id = cls.assigned_user_id 

left outer join (meetings as mtn 
    inner join meetings_users as mtnusr 
    on mtn.id = mtnusr.meeting_id) 
on usr.id = mtn.assigned_user_id 

left outer join tasks as tsk 
on usr.id = tsk.assigned_user_id 

left outer join (notes as nts 
    inner join accounts as acnts 
    on acnts.id=nts.parent_id) 
on usr.id = acnts.assigned_user_id 

where user.id = 'seed_max_id'