[RESOLVED] Searching Lists - 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 2005 Forums

Transact-SQL (2005)

[RESOLVED] Searching Lists

Author

Topic

dnig
Starting Member

8 Posts

Posted - 2010-02-03 : 07:58:17

Hey all,

Probably an easy one for you gurus. The hamster isn't running fast enough and I can't think of the answer.

I have a list of strings I need to search for, but I need the results to be returned in the order given and with blanks (or NULLS) if the string was not found.

e.g.

Strings to search for A, B, C, D, E

Results would return:

A John
B NULL <or blank or whatever>
C Mary
D Peter
E NULL

Thanks

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-02-03 : 08:34:38

SELECT * FROM table WHERE Letter IN ('A', 'B', 'C', 'D', 'E')

...??

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

dnig
Starting Member

8 Posts

Posted - 2010-02-03 : 08:56:42

quote:
Originally posted by Lumbago

SELECT * FROM table WHERE Letter IN ('A', 'B', 'C', 'D', 'E')

...??

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

That won't return the results as specified above.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-03 : 09:27:35

You need something like

select t1.Letter,t1.othercol from your_table as t1
left outer join
(
select 'A' as Letter union all
select 'B' union all
select 'C' union all
select 'D' union all
select 'E'
) as t2
on t1.Letter=t2.Letter
Order by t1.Letter

Madhivanan

Failing to plan is Planning to fail

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-02-03 : 09:29:11

From your descriptions it will. Please post a sample table with some data if you want a more accurate response.

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

dnig
Starting Member

8 Posts

Posted - 2010-02-03 : 09:50:12

Hi Lumbago,

From the example results I've shown it wouldn't. Your code would have omitted results for B & E. Where I clearly wanted them to be shown as blanks or Nulls.

dnig
Starting Member

8 Posts

Posted - 2010-02-03 : 09:51:32

Hi madhivanan,

Thanks for the solution, the only change I made was to change the left outer join to a right outer join and this gave me the results I needed

Many Thanks

dNig

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-03 : 10:05:45

quote:
Originally posted by dnig

Hi madhivanan,

Thanks for the solution, the only change I made was to change the left outer join to a right outer join and this gave me the results I needed

Many Thanks

dNig

You are welcome

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources