K
Kevin Brown
I want to write a query which involves 4 tables and outer joins, but I'm
just not getting it right. I'll list 4 example tables and an example
desired result. Any ideas?
Scenarios (id)
----------
S1
S2
S3
S4
S5
Groups (id)
--------
G1
G2
G3
Members (group_id, player_id)
----------
G1, P1
G1, P2
G1, P3
G2, P1
PlayerHistory (player_id, scenario_id)
--------------
P1, S1
P1, S2
P1, S3
P2, S1
P3, S1
Desired Results (group_id, scenario_id, count)
----------------
G1 S1 Count=3 (because P1/P2/P3 played S1)
G1 S2 Count=1 (because P1 played S2)
G1 S3 Count=1 (because P1 played S3)
G1 S4 Count=0
G1 S5 Count=0
G2 S1 Count=1 (because P1 played S1)
G2 S2 Count=1 (because P1 played S2)
G2 S3 Count=1 (because P1 played S3)
G2 S4 Count=0
G2 S5 Count=0
G3 S1 Count=0
G3 S2 Count=0
G3 S3 Count=0
G3 S4 Count=0
G3 S5 Count=0
I want the results to list every scenario for every group and give a count
of how many members of that group played that scenario. In this example: 3
groups * 5 scenarios = 15 rows. Each row having a count of the players of
that group who played that scenario.
Thoughts?
just not getting it right. I'll list 4 example tables and an example
desired result. Any ideas?
Scenarios (id)
----------
S1
S2
S3
S4
S5
Groups (id)
--------
G1
G2
G3
Members (group_id, player_id)
----------
G1, P1
G1, P2
G1, P3
G2, P1
PlayerHistory (player_id, scenario_id)
--------------
P1, S1
P1, S2
P1, S3
P2, S1
P3, S1
Desired Results (group_id, scenario_id, count)
----------------
G1 S1 Count=3 (because P1/P2/P3 played S1)
G1 S2 Count=1 (because P1 played S2)
G1 S3 Count=1 (because P1 played S3)
G1 S4 Count=0
G1 S5 Count=0
G2 S1 Count=1 (because P1 played S1)
G2 S2 Count=1 (because P1 played S2)
G2 S3 Count=1 (because P1 played S3)
G2 S4 Count=0
G2 S5 Count=0
G3 S1 Count=0
G3 S2 Count=0
G3 S3 Count=0
G3 S4 Count=0
G3 S5 Count=0
I want the results to list every scenario for every group and give a count
of how many members of that group played that scenario. In this example: 3
groups * 5 scenarios = 15 rows. Each row having a count of the players of
that group who played that scenario.
Thoughts?