First, I am a noob, just trying to learn some Access/VBA/SQL, but I have been stumped by this issue. Shown below are the (9) tbl JOIN relations.
This lists X
records for each game_ID
(X = number of players in game). I only want one record per game_ID
where
[game_players.player_ID]
is a selected/special player (say, HERO )[game_players_1.player_ID]
is WINNER (who is NOT HERO, i.e. VILLAIN )
I am attempting to select ::: where
[game players_2.player_ID] is 2nd BEST Villain
(if there is one, otherwise, NULL-field)
I am trying to replace [game_players_2]
with a DERIVED-TABLE for an OUTER JOIN because, when I use the limiting subquery (shown below) in WHERE clause (or QBE : criteria for [game_players_2.player_ID]
) the OUTER JOIN behaves like INNER JOIN (i.e. only records with 2 or more Villain WINNERS are displayed, records with only (1) villain records are NOT output by query.
If you need more details/code, let me know....
================= JOIN RELATIONS ====================
FROM players AS players_2
RIGHT JOIN (game_players AS game_players_2
RIGHT JOIN (players
INNER JOIN (game
INNER JOIN ([session]
INNER JOIN (HandCalcs
INNER JOIN (HandCalcs AS HandCalcs_1
INNER JOIN (game_players AS game_players_1
INNER JOIN game_players
ON game_players_1.game_id = game_players.game_id)
ON (HandCalcs_1.player_ID = game_players_1.player_id) AND
(HandCalcs_1.game_ID = game_players_1.game_id))
ON (HandCalcs.player_ID = game_players.player_id) AND
(HandCalcs.game_ID = game_players.game_id))
ON session.session_id = game_players.session_id)
ON game.game_id = game_players.game_id)
ON players.player_id = game_players_1.player_id)
ON game_players_2.game_id = game_players.game_id)
ON players_2.player_id = game_players_2.player_id
============== subquery / (? defined table ) ====================
( SELECT TOP 1 DUP2.player_id
FROM HandCalcs AS DUP2
WHERE (
((DUP2.player_id)<>[Forms]![Combo2]![cboName_PID]) AND
((DUP2.game_id)=(game_players.game_id)) AND
((DUP2.player_ID) <> ( game_players_1.player_id)) AND
((DUP2.HV_CatctusKev_R) Is not Null)
)
ORDER BY DUP2.HV_CatctusKev_R ASC , DUP2.player_id
)
================================================================