Basic SQL Question - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Basic SQL Question

Author

Topic

Firemaple
Starting Member

14 Posts

Posted - 2011-10-31 : 09:45:16

I realize this is pretty basic, but I can't figure out how to word it to find a decent search result.

Basically, I just want to query my table for a set of values, and return the results or nulls if that value is not found. For instance.

Id | Name | Job
-----------------------
1 bob Analyst
2 jane Technician
3 george Janitor

select * from table1 where name in ('bob', 'jane', 'paul', 'mark')

I would like the return to be

Id | Name | Job
-----------------------
1 bob Analyst
2 jane Technician
NULL paul NULL
NULL mark NULL

Any suggestions, I'm sure i am just over thinking this.
Thank you!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-31 : 09:57:43

;with cte as
(
select name = 'bob'
union all
select 'jabe'
union all
select 'paul'
union all
select 'mark'
)
select t2.id, t1.name, t2.job
from cte t1
left join mytbl t2
on t1.name = t2.name

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-31 : 11:23:01

more generic solution


DECLARE @strlist varchar(2000)

SET @strlist = 'bob,jane,paul,mark'--pass any number of values as comma seperated through this

SELECT t.ID,f.Val AS Name,t.Job
FROM dbo.ParseValues (@strlist,',')f
LEFT JOIN yourtable t
on t.name = f.Val

ParseValues is string parsing udf which can be found in below link

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources