如何加入两个查询 [英] how to join two querries

问题描述

大家好！
我创建两个查询
在querry1的条件下，我想添加querry2
querry1仅显示一条记录
并将querry2添加到querry1中，它将显示多个行

query1

hi to all!
i create two querries
in querry1''s where condition i want to add querry2
querry1 is to show only one record
and by adding querry2 to querry1 it will show multiples rows

query1

ALTER procedure [dbo].[proc_mrf_discription_user1] (@mrfid int)
as
select sec_modules.modname_vc, 
	   sec_roles.rolename_vc, 
	   sec_functions.functionname_vc
from sec_umrf_details
left join sec_mrf_details on sec_umrf_details.mrfno_int = sec_mrf_details.mrfno_int
left join sec_modules on sec_modules.modid_int = sec_mrf_details.modid_int
left join sec_roles on sec_roles.roleid_int = sec_mrf_details.roleid_int
left join sec_functions on sec_functions.fnid_int = sec_mrf_details.fnid_int

where 

sec_mrf_details.mrfno_int = (@mrfid)

group by sec_modules.modname_vc, sec_roles.rolename_vc, sec_functions.functionname_vc

而不是这个"sec_mrf_details.mrfno_int = (@mrfid)"
我想添加querry2，但请记住querry2有多行
query2

instead of this "sec_mrf_details.mrfno_int = (@mrfid)"
i want to add querry2 but remember querry2 has multiple rows
query2

select sec_umrf_details.mrfno_int from sec_umrf_details
where userid_vc = 'asda'

推荐答案

快速阅读问题的最简单方法是使用嵌套查询(尽管一般不建议这样做).

Simplest way with a quick read of the question is to use nested query (it is not advisable generally though).

select columns from table where someColumn in (select someColumn from table where someCondition)

另一种方法是也使用适当的联接.这将比以前更有效.

Another way will be too use proper joins. This is will be more efficient than former.

尝试一下.

Try this.

ALTER procedure [dbo].[proc_mrf_discription_user1] (@mrfid int)
as
select sec_modules.modname_vc, 
	   sec_roles.rolename_vc, 
	   sec_functions.functionname_vc
from sec_umrf_details
left join sec_mrf_details on sec_umrf_details.mrfno_int = sec_mrf_details.mrfno_int
left join sec_modules on sec_modules.modid_int = sec_mrf_details.modid_int
left join sec_roles on sec_roles.roleid_int = sec_mrf_details.roleid_int
left join sec_functions on sec_functions.fnid_int = sec_mrf_details.fnid_int
 
where 
 
sec_mrf_details.mrfno_int IN (select sec_umrf_details.mrfno_int from sec_umrf_details
where userid_vc = ''asda'')
 
group by sec_modules.modname_vc, sec_roles.rolename_vc, sec_functions.functionname_vc

如果要累积两个返回相似结果的查询的结果，可以使用UNION 操作.

作为示例，请参见此

If you want to accumulate the result of two queries that return similar results, you can use the UNION operation.

As an example, see this

select sec_modules.modname_vc, 
	   sec_roles.rolename_vc, 
	   sec_functions.functionname_vc,'',''
from sec_umrf_details
left join sec_mrf_details on sec_umrf_details.mrfno_int = sec_mrf_details.mrfno_int
left join sec_modules on sec_modules.modid_int = sec_mrf_details.modid_int
left join sec_roles on sec_roles.roleid_int = sec_mrf_details.roleid_int
left join sec_functions on sec_functions.fnid_int = sec_mrf_details.fnid_int
where 
sec_mrf_details.mrfno_int = (@mrfid)
group by sec_modules.modname_vc, sec_roles.rolename_vc, sec_functions.functionname_vc
UNION
select '','','',sec_umrf_details.mrfno_int from sec_umrf_details
where userid_vc = 'asda'

这篇关于如何加入两个查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！