Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003

    Unanswered: Select within case statement?

    Limitations: Needs to be single sql statement for exec, no udf/stored proc
    Objective: select aggregate count from tblPerson grouped by s_fk, division,Desc, areaDesc with rollup for totals
    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

    What's the best method? A case statement? Would a cross-join work? Thanks so much for any and all advice!

    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

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    use a UNION | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts