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.

:-/