Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    43

    Unanswered: Cross Join without Table?

    I have the following structure with remote select permissions; I cannot create temp tables or use stored procs:

    tblEvent with event_pk, eventName
    tblReg with reg_pk, event_fk, person_fk, organization_fk

    I'm currently using a case statement to get counts for these categories:
    case
    when c.person_fk is Null and c.organization_fk is not null then 'Employer'
    when c.person_fk is Not Null and c.organization_fk is null then 'Individual'
    when c.person_fk is not Null and c.organization_fk is not null then 'Both'
    else 'Unknown'
    end

    But I need some kind of count (0) for every category. I've used a cross-join, group by in the past - but what do you do if you don't have a table? For example, the end result when selecting event_pk=(112,113) would be:

    event_pk, myCount, countCat
    112 0 Employer
    112 1 Individual
    112 4 Both
    112 0 Unknown
    113 5 Employer
    113 0 Individual
    113 0 Both
    113 2 Unknown

    Thanks for any help,
    jb

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Dear Lord, save us from those who would require us to eat sphagetti with chopsticks, swim with boxing gloves, and develop databases without stored procs or temporary tables. And forgive them, for they know not what the hell they are doing.

    Fortunately for you, it is probably possible to get a reasonable solution for your problem even without using stored procs or temp tables.

    I think this will work....

    Code:
    Select SubQuery.event_pk, isnull(SubQuery.myCount, 0) as myCount, RegTypes.countCat
    From	(Select 'Employer' as countCat
    	UNION
    	Select 'Individual' as countCat
    	UNION
    	Select 'Both' as countCat) RegTypes
    	Left outer join
    		(Your Query Goes Here) SubQuery
    		on RegTypes.countCat = RegTypes.countCat
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Oct 2003
    Posts
    43
    You're to best. Didn't realize you could "create" a derrived table without selecting at least one field from a real one. Thanks so much.

    It is by coffee alone I set my mind in motion. It is by the beans of java that thoughts acquire speed. The hands acquire shaking. The shaking is a warning. It is by coffee alone I set my mind in motion.

Posting Permissions

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