Hairy SQL select using CASE statement

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 2000 Forums

Transact-SQL (2000)

Hairy SQL select using CASE statement

Author

Topic

kathunter
Starting Member

35 Posts

Posted - 2002-08-04 : 20:40:17

I have 2 tables. One lists packageIDs and a rank, the other lists packageIDs and a feature date.

I want to return a recordset where
oRS("packageID") = (packageID#)
oRS("rank") = (rank#)
oRS("featured")= True or False

Table Structure (tblList)
rank, packageID, description...

Table Structure (tblFeatured)
packageID, featureddate

My Stored procedure is:
SELECT packageID, rank, description
FROM tblList WHERE rank >10

but I need to encorporate a CASE clause to return a true or false value for the featured attribure of each pachageID in the records set. Everything I have tried has not worked... Any thoughts?

r937
Posting Yak Master

112 Posts

Posted - 2002-08-04 : 21:31:32

SELECT packageID, rank, description 
     , CASE WHEN F.packageID IS NULL
        THEN "False" ELSE "True"
       END as Featured
  FROM tblList L
LEFT JOIN tblFeatured F
    ON L.packageID = F.packageID
 WHERE rank >10

add GROUP BY packageID, rank, description
if the package can be featured on more than one date

caution: not tested

rudy
http://rudy.ca/

kathunter
Starting Member

35 Posts

Posted - 2002-08-04 : 22:06:24

But this does not take into account the date? Each day this feed is generated, and the featured slot will change.
Can I use a select statement in the case?
CASE WHEN (Select packageID from lstFeatured where
featureddate = getdate()) THEN "True" ELSE "FALSE"

quote:

SELECT packageID, rank, description
, CASE WHEN F.packageID IS NULL
THEN "False" ELSE "True"
END as Featured
FROM tblList L
LEFT JOIN tblFeatured F
ON L.packageID = F.packageID
WHERE rank >10
add GROUP BY packageID, rank, description
if the package can be featured on more than one date

caution: not tested

rudy
http://rudy.ca/

I should have taken the blue pill

r937
Posting Yak Master

112 Posts

Posted - 2002-08-04 : 22:45:35

you never said anything about which date

the left join accomplishes what you want -- dunno about the subselect, it just feels awkward to me

in the left join, if a row from tblList does not have a matching row from tblFeatured, then F.packageID will be null, so you get False

if there is a matching row, based on packageID only, then you get True -- at least, in the query as it was up to this point

so, just add another condition to the left join -- the tblList row has to join to a tblFeatured row with today's date

SELECT packageID, rank, description 
     , CASE WHEN F.packageID IS NULL
        THEN "False" ELSE "True"
       END as Featured
  FROM tblList L
LEFT JOIN tblFeatured F
    ON L.packageID = F.packageID
   AND F.featureddate = getdate()
 WHERE rank >10

rudy
http://rudy.ca/

Edited by - r937 on 08/05/2002 09:36:43

Edited by - r937 on 08/05/2002 09:37:14

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources