Help writing a query

[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?

 

[Brian]

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?

Possibly something along these lines. The join expression is probably going
to want some parentheses somewhere but, if you understand what I'm
suggesting, you should be able to put it together in the query designer.
The key to it is that it's a CROSS JOIN between Groups and Scenarios (i.e.
there would be NO join line in the query designer):

SELECT G.id, S.id, Count(H.player_id) FROM Groups G, Scenarios S LEFT JOIN
Members M ON G.group_id = M.group_id LEFT JOIN PlayerHistory H ON
M.player_id = H.player_id GROUP BY G.id, S.id

 

[Kevin Brown]

Possibly something along these lines. The join expression is
probably going to want some parentheses somewhere but, if you
understand what I'm suggesting, you should be able to put it together
in the query designer. The key to it is that it's a CROSS JOIN
between Groups and Scenarios (i.e. there would be NO join line in the
query designer):

SELECT G.id, S.id, Count(H.player_id) FROM Groups G, Scenarios S LEFT
JOIN Members M ON G.group_id = M.group_id LEFT JOIN PlayerHistory H ON
M.player_id = H.player_id GROUP BY G.id, S.id

I understand why you want the CROSS JOIN - to acheive a cartesian product
between groups and scenarios yielding the 15 rows in my example above. I've
tried putting parens as I thought appropriate around the joins, but I keep
ending up with syntax errors.

Here is my current guess (yielding a syntax error):
SELECT G.id, S.id, Count(H.player_id)
FROM Groups G,
(Scenarios S LEFT JOIN Members M ON G.id = M.group_id) LEFT JOIN
PlayerHistory H ON M.player_id = H.player_id
GROUP BY G.id, S.id

Thoughts?

 

[Brian]

I understand why you want the CROSS JOIN - to acheive a cartesian product
between groups and scenarios yielding the 15 rows in my example above. I've
tried putting parens as I thought appropriate around the joins, but I keep
ending up with syntax errors.

Here is my current guess (yielding a syntax error):
SELECT G.id, S.id, Count(H.player_id)
FROM Groups G,
(Scenarios S LEFT JOIN Members M ON G.id = M.group_id) LEFT JOIN
PlayerHistory H ON M.player_id = H.player_id
GROUP BY G.id, S.id

Thoughts?

The join is too ambitious for Access. The thing to do is to create and save
the cross join as a separate query, and then use that in your main query
instead of the Groups and Scenarios tables. If you're determined to do it
in one query, then you'll need to put the cross join in a subquery.

 

[Kevin Brown]

The join is too ambitious for Access. The thing to do is to create
and save the cross join as a separate query, and then use that in
your main query instead of the Groups and Scenarios tables. If
you're determined to do it in one query, then you'll need to put the
cross join in a subquery.

I tried that and still no luck. However... I decided to create a 3rd query
and did have luck.

This is what I eventually created
:
qCartesianGroupsScenarios
----------------------------
SELECT Groups.ID AS Group_ID, Scenarios.ID AS Scenario_ID, Scenarios.Name AS
Scenario_Name
FROM tGroups, tScenarios;

qMembersHistory
------------------
SELECT Members.Player_ID, Members.Group_Name, PlayerHistory.Scenario_ID
FROM Members INNER JOIN PlayerHistory ON Members.Player_ID =
tPlayerHistory.Player_ID;

qScenariosPlayedByGroupsCOUNT (returns the results I wanted)
--------------------------------------------------------------------
SELECT GS.Group_ID, GS.Scenario_ID, GS.Scenario_Name, Count(MH.Player_ID) AS
CountOfPlayers
FROM qCartesianGroupsScenarios AS GS LEFT JOIN qMembersHistory AS MH ON
(GS.Scenario_ID=MH.Scenario_ID) AND (GS.Group_Name=MH.Group_Name)
GROUP BY GS.Group_Name, GS.Scenario_ID, GS.Scenario_Name;


Thanks.