Combination

[kalkumar]

Hi,
I have the table:
exexposureRoute: with columns
caseId, exRouteID
200 70
300 71
200 72
200 73
.. ..

Route table:
exRoteID name
70 a
71 b
72 c
73 d
74 e

and so on



Outcome table

OutomceID name

1 Outcome1

2 Outcome2

..




In my result set i want like this:


Routes Outcome1 Outcome2 Outcome3

a

b

c

d

e

f

g

h

a/c

a/c/b

b/c/e

a/e

a/b

a/d

a/b/d

a/b/e

a/d/e

a/b/d/e

b/e

b/d

b/d/e

d/e



I need to get only those combinations not all the Possible combinations.
in the above a/b means i want the casecount for which cases both a and b exists for each outcome type.
For one case there mey be possibilty of more than one route.

How to write the query to get those routes combinations case counts.

Thanks in advance

 

[rac2]

Probably by writing several queries and combining them with a UNION. Unless there is some pattern in the combinations of routes of interest.

How do you associate an outcome with a case?

 

[kalkumar]

For each case there is a outcome associated to it.

 

[rac2]

Would that association be in the Outcome table and if so, is there a column for caseid in that table?

Is there a fixed set of outcomes and always the same number per case?