Return valuse based on multiple "if exists' statements
in [Using MS SQL Server]
|
Prev: Installation Problem SQL Server 2005 + Windows 7
Next: Return valuse based on multiple "if exists' conditions (Advanced)
From: SQL Learner on 6 Feb 2010 14:43 Hi All, Hope someone can help to solve this SQL question. I have the following table called '#temptable' (it is a temp table) source price state A 1 NY B 2 NY C 3 NY B 4 CA C 5 CA C 6 TX I need a SQL statement that performs the following pesudo code: if source A exists then (select * from #temptable where source = 'A') if source A not exists then check if source B exist. if source B exists then (select * from #temptable where source = 'B') if source B not exists then check if source C exist. if source C exists then (select * from #temptable where source = 'C') if source C not exists then print 'Nothing is found.' The following code can be used to create the temp table for testing: create table #TempTable (source char(1), price int, state char (2)) insert into #TempTable values ('A', 1, 'NY') insert into #TempTable values ('B', 2, 'NY') insert into #TempTable values ('C', 3, 'NY') insert into #TempTable values ('B', 4, 'CA') insert into #TempTable values ('C', 5, 'CA') insert into #TempTable values ('C',6, 'TX') Thanks. SQL Learner
From: SQL Learner on 6 Feb 2010 15:23 I did some experiment with the code and found the solution: ================================================ if exists ( select * from #TempTable where source = 'A') select * from #TempTable where source = 'A' else if exists ( select * from #TempTable where source = 'B') select * from #TempTable where source = 'B' else if exists ( select * from #TempTable where source = 'C') select * from #TempTable where source = 'C' else print 'Nothing is found.' ================================================ However, I know there are different approaches to this problem. If anyone know of different ways (probably more advanced), please share. Thanks. SQL Learner
From: Hugo Kornelis on 6 Feb 2010 17:42 On Sat, 6 Feb 2010 11:43:38 -0800 (PST), SQL Learner wrote: >Hi All, > >Hope someone can help to solve this SQL question. > >I have the following table called '#temptable' (it is a temp table) > >source price state >A 1 NY >B 2 NY >C 3 NY >B 4 CA >C 5 CA >C 6 TX > >I need a SQL statement that performs the following pesudo code: > >if source A exists then (select * from #temptable where source = 'A') >if source A not exists then check if source B exist. >if source B exists then (select * from #temptable where source = 'B') >if source B not exists then check if source C exist. >if source C exists then (select * from #temptable where source = 'C') >if source C not exists then print 'Nothing is found.' > >The following code can be used to create the temp table for testing: > >create table #TempTable (source char(1), price int, state char (2)) >insert into #TempTable values ('A', 1, 'NY') >insert into #TempTable values ('B', 2, 'NY') >insert into #TempTable values ('C', 3, 'NY') >insert into #TempTable values ('B', 4, 'CA') >insert into #TempTable values ('C', 5, 'CA') >insert into #TempTable values ('C',6, 'TX') > >Thanks. > >SQL Learner > Hi SQL Learner, How about: SELECT source, price, state FROM #TempTable WHERE source = (SELECT MIN(source) FROM #TempTable); -- Hugo Kornelis, SQL Server MVP My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
From: Plamen Ratchev on 6 Feb 2010 17:43 This should do it: SELECT TOP 1 WITH TIES source, price, state FROM #TempTable WHERE source IN ('A', 'B', 'C') ORDER BY source; -- Plamen Ratchev http://www.SQLStudio.com
From: SQL Learner on 6 Feb 2010 18:38
Hi Hugo and Plamen, Thank you guys for your solution. What if I change the data to this: Tiger 1 NY Ape 2 NY Elephant 3 NY Ape 4 CA Elephant 5 CA Elephant 6 TX Just substitute Tiger for A, Ape for B, and Elephant for C. This way, the Top 1 and Min methods will not work, right? How can we modify the code to accommendate for this change? Thank you in advanced. SQL Learner |