how to join two querries

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

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

SQL

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

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

SQL

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

Posted 23-Aug-11 20:58pm

kami124

Updated 23-Aug-11 21:02pm

Prerak Patel

v2

Add a Solution

Comments

Prerak Patel 24-Aug-11 3:02am

Format your question properly. Use code block for code segments.

4 solutions

Solution 2

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

SQL

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'

Posted 23-Aug-11 21:06pm

Abhinav S

v2

Comments

kami124 24-Aug-11 3:15am

i think you didnt read my question properly how they are similsr

now listen querry 2 i will get @mrfid

and by that mrfid querry 1 wil display complete row

so in querry2 we have more then one values

Solution 4

select field1.Table1, field1.Table2 from Table1 Leftjoin Table2 on field.Table1=field.Table2

Posted 23-Aug-11 23:25pm

santhosh1985april

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

*dan!sh* · Accepted Answer · 2011-08-23T21:01:00

Solution 1

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

SQL

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.

Posted 23-Aug-11 21:01pm

dan!sh

Comments

Toniyo Jackson 24-Aug-11 3:50am

Correct solution, 5!

Toniyo Jackson · Accepted Answer · 2011-08-23T21:49:00

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

how to join two querries

4 solutions

Solution 1

Solution 3

Solution 2

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us