T-SQL CASE ? - 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)

T-SQL CASE ?

Author

Topic

JennyGW
Starting Member

2 Posts

Posted - 2011-05-22 : 18:29:44

Hi all,

Hope someone can help me with this little brain teaser ...

I have 3 tables, for example, A/B/C

I am using tables A and B to run a query, with a straightforward inner join.

Now, I want to add some WHERE conditions based on a value in table C.

Here is the query for A and B, simple stuff:

SELECT tbl_stock.stockID, tbl_stock.stockTitle, tbl_stockItems.stockItemID, tbl_stockItems.itemDescr
FROM tbl_stock INNER JOIN tbl_stockItems ON tbl_stock.stockID = dbo.tbl_stockItems.stockID

IF tableC.someColumn = 'someValue' THEN where TableA.somecolumn != 0
ELSEIF tableC.someColumn = 'someothervalue' THEN where TableA.somecolumn > 10

IF tableC.someotherColumn = 'thisvalue' THEN where 

I've been looking for an answer to this for a few days. It seems that CASE comes closest to what I need, but I can't see how to get the syntax!

I could do what I have always done and run the rest of the query within Coldfusion using CFIF's, but I think this is messy and I would prefer to keep the whole query in SQL, and I'm hoping there would be some performance gain, as well.

Thanks in advance for any ideas!

Jenny

Jenny

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-22 : 19:47:11

CASE is probably the way to go. Your psuedo-code is too vague for us to program for. But A and B will need to join to it some how.

SELECT
<stuff>
,CASE WHEN C.SomeColumn = 'someValue' and a.somecolumn <> 0 THEN < need a value here, which table/column?>
WHEN C.someColumn = 'someOtherValue' and a.someColumn > 10 THEN < need a value here, which table/column?>
END
A inner join B on <stuff>
inner join C on stuff

Jim

Everyday I learn something that somebody else already knew

JennyGW
Starting Member

2 Posts

Posted - 2011-05-23 : 07:48:09

Hi Jim,

Many thanks for your reply ..

Someone in my CF user group was able to come up with my answer:

select projected from tbl_stockItems
where
(
(SELECT checkProjected FROM tbl_Params WHERE siteID = 1) = 1 AND
projected > 10
)
OR
(
(SELECT checkProjected FROM tbl_Params WHERE siteID = 1) <> 1 AND
projected < 10
)

Jenny

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources