Case Select within Select? - 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 2000 Forums

SQL Server Development (2000)

Case Select within Select?

Author

Topic

janetb
Yak Posting Veteran

71 Posts

Posted - 2005-01-20 : 16:47:55

What's the best method for getting at the senario below? A case statement? Would a cross-join work? Am I nuts? Limitations: Needs to be single sql statement for execution, no udf/no stored proc Objective: select aggregate count from tblPerson grouped by s_fk, division,Desc, areaDesc with rollup for totals

Thanks so much for any and all advice!

if mail_goes_to=1, get the s_fk value via tblPerson/tblAffiliation/tblOrg/tblCity/tblCounty
if mail_goes_to=2, get the s_fk value via tblPerson/tblCity/tblCounty, skipping tblAffiliation/tblOrg

tblPerson: person_pk, affiliation_fk; home_city_fk, mail_goes_to
tblAffiliation: affiliation_pk, organization_fk
tblOrg: organization_pk, city_fk
tblCity: city_pk, county_fk
tblCounty: county_pk, s_fk

For resulting display only:
tblPersonArea: personArea_pk, area_fk, person_fk
tblArea: area_pk, areaDesc, division_fk
tblDivision: division_pk, divisionDesc

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-20 : 16:49:30

Huh? Could you explain with DDL of your tables (CREATE TABLE statements) and some sample data? Have you looked at CASE in SQL Server Books Online?

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-20 : 16:51:59

I would use a UNION...

What's the Course Title Janet?

Brett

8-)

janetb
Yak Posting Veteran

71 Posts

Posted - 2005-01-20 : 17:03:04

OOOOH, Union, of course. Brain fart. Thanks Brett

No course, just learnin' as I go by the seat of my pants. See, no pride - just trying to make it too complicated. Thanks so much for the quick tongue-in-e-cheek response.

I need a book with some complicated select scenarios to ponder (the sql books seem to be the straight-forward kind that you almost never run into in the real world - at least my boss doesn't come up with them). Recommendations?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-20 : 17:05:42

Ken Henderson's books!

Here's the ones SQLTeam recommends:
http://www.sqlteam.com/store.asp

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources