Return value only if exist - 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)

Return value only if exist

Author

Topic

swirls
Starting Member

5 Posts

Posted - 2010-06-22 : 01:43:43

Am wondering if anyone can help me here.

What I'm trying to do is get the query to return the associated row value where a particular FieldID exists.

For example, I have the following Table A with data as per below:

OrderID FieldID FieldValue
------- ------- ----------
11111 123 Install
11111 234 Version 1
11111 NAME John Smith
11111 ADD Station Street
22222 123 Pickup
22222 234 Version 2
33333 NAME Jane Doe

I want the query to return values in the OrderID and FieldValue column where FieldID = 'NAME', and if FieldID = 'NAME' doesn't exist, return a blank. The result of the query should be the following:

OrderID FieldValue
------- ----------
11111 John Smith
22222
33333 Jane Doe

I have built the following query:

SELECT
A.OrderID,
CASE WHEN A.FieldID = 'QNAME' THEN A.FieldValue ELSE '' END

FROM dbo.A

However, the above will return the following instead:

OrderID FieldValue
------- ----------
11111
11111
11111 John Smith
11111
22222
22222
33333 Jane Doe

Seems to be returning everything in the table except substituting the FieldValue with blanks when NAME is not found.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-22 : 02:10:44

[code]
-- making test data
declare @test table (OrderID int, FieldID varchar(255), FieldValue varchar(255))
insert @test
select 11111, '123', 'Install' union all
select 11111, '234', 'Version 1' union all
select 11111, 'NAME', 'John Smith' union all
select 11111, 'ADD', 'Station Street' union all
select 22222, '123', 'Pickup' union all
select 22222, '234', 'Version 2' union all
select 33333, 'NAME', 'Jane Doe'

-- the solution
select OrderID, FieldValue from @test
where FieldID = 'NAME'
union
select OrderID, '' from @test t1
where not exists(select * from @test t2 where t2.OrderID=t1.OrderID and t2.FieldID='NAME')[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

Llewellyn
Starting Member

13 Posts

Posted - 2010-06-23 : 10:44:01

select distinct OrderID, case when FieldID = 'NAME' then FieldValue else '' end
from TableA
where FieldID = 'NAME'
or OrderID not in (SELECT OrderID FROM TableA where FieldID = 'NAME')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources