Sql Query Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sql Query Help

Author

Topic

mmkrishna1919
Yak Posting Veteran

95 Posts

Posted - 2013-08-06 : 12:32:35

Hi Team,

i have below list of queries how can i combine these 3 queries into one sql query with emp_id column values into respective variables?

declaration of variables followed these 3 queries

select @emp_id1=emp_id
from emp e,dept d
where e.dept_no=d.dept_no
and dept_name='science'
and empname='david'

select @emp_id2=emp_id
from emp e,dept d
where e.dept_no=d.dept_no
and dept_name='science'
and empname='rob'

select @emp_id1=emp_id
from emp e,dept d
where e.dept_no=d.dept_no
and dept_name='science'
and empname='sofia'

Thanks,

M.MURALI kRISHNA

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-06 : 12:58:44

Use CASE statement :

[CODE]
select @emp_id1 = (CASE WHEN empname='david' THEN emp_id ELSE @emp_id1 END),
@emp_id2 = (CASE WHEN empname='rob' THEN emp_id ELSE @emp_id2 END),
@emp_id3 = (CASE WHEN empname='sofia' THEN emp_id ELSE @emp_id3 END)
from emp e,dept d
where e.dept_no=d.dept_no
and dept_name='science'
[/CODE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-07 : 00:57:44

you may be better off putting all the required values in table along with id if you've lots of them rather than using variables for each. Then its just a matter of adding a join with tables to retrieve required details.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mmkrishna1919
Yak Posting Veteran

95 Posts

Posted - 2013-08-07 : 01:55:09

Hi MuMu88 and visakh

thanks for your reply it's working..

MuMu88:
My empname column has the hundread of values out of those one of the value is 'David'.

sample empname column data:
val1
val2
.
.
.
DavidVal100
.
.
Val10000

please clarify

if case statement finds the condition mentioned in when (WHEN empname='david' ) then it should return value(empid) and terminate the execution of that case statement?

how the execution is going on?

Performance:the three statements using joins have better execution than single query with CASE statement why?

Thanks.

M.MURALI kRISHNA

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-07 : 02:12:19

@emp_id1 = (CASE WHEN empname='david' THEN emp_id ELSE @emp_id1 END)
If the employee David existed in the table then gives you the corresponding employee id to @emp_id1; otherwise returns @emp_id1 value ( in the case of, you initialized value for the variable @emp_id1 while declaration)

--
Chandu

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-07 : 03:20:56

[code]SELECT @Emp_ID1 = MAX(CASE WHEN e.EmpName = 'David' THEN e.Emp_ID ELSE NULL END),
@Emp_ID2 = MAX(CASE WHEN e.EmpName = 'Rob' THEN e.Emp_ID ELSE NULL END),
@Emp_ID3 = MAX(CASE WHEN e.EmpName = 'Sofia' THEN e.Emp_ID ELSE NULL END)
FROM dbo.Emp AS e
INNER JOIN dbo.Dept AS d ON d.Dept_No = e.Dept_No
WHERE d.Dept_Name = 'Science'
AND e.EmpName IN ('David', 'Rob', 'Sofia');[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-07 : 06:29:55

quote:
Originally posted by mmkrishna1919

Hi MuMu88 and visakh

thanks for your reply it's working..

MuMu88:
My empname column has the hundread of values out of those one of the value is 'David'.

sample empname column data:
val1
val2
.
.
.
DavidVal100
.
.
Val10000

please clarify

if case statement finds the condition mentioned in when (WHEN empname='david' ) then it should return value(empid) and terminate the execution of that case statement?

how the execution is going on?

Performance:the three statements using joins have better execution than single query with CASE statement why?

Thanks.

M.MURALI kRISHNA

How many conditions you're checking for in CASE? also whats the amount of data over which conditional logic is applied?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources