Ok, I've hit the wall here and need some help. Sample tables are as follows:
SCENARIO_NATIONS
[scenID] [side] [nation]
scen001 1 Germany
scen001 2 Britain
scen001 2 Canada
SCENARIO_NEEDUNITS
[scenID] [unitID]
scen001 0001
scen001 0003
scen001 0107
scen001 0258
scen001 0759
UNIT_BASIC_DATA
[unitID] [nation] [name]
0001 Germany Mortars
0003 Germany Infantry
0107 Britain Lt
0258 Britain Infantry
0759 Canada Kilted Yaksmen
Goal: given a scenID, pull a list of units from the database sorted by side, nation, name.
I can do everything except for the side inclusion with:
SELECT scenario_needunits.scenID, unit_basic_data.nation, unit_basic_data.name
FROM scenario_needunits
LEFT OUTER JOIN unit_basic_data
ON scenario_needunits.unitID=unit_basic_data.unitID
WHERE scenario_needunits.scenID='scen001'
ORDER BY unit_basic_data.nation ASC, unit_basic_data.name ASC
I've tried just dropping the SCENARIO_NATIONS table in as a LEFT OUTER JOIN on scenID but what ends up happening is that ALL units come back with a side of 1 because that's always the first side listed for the scenID in the SCENARIO_NATIONS table.
Conceptually, what I think needs to happen is SCENARIO_NATIONS must be joined to both the scenID (to restrict it to just that scenario) and to each unit's nation but I don't have any idea how to do that and my Google-fu is inadequate.
:-/
