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 |
+-------------------------------------------+------------------+-------------------+-------------------+
2 个解决方案
#1
5
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.
不需要额外的复杂性,上面的内容显然更具可读性。
#2
0
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'
#1
5
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.
不需要额外的复杂性,上面的内容显然更具可读性。
#2
0
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'