How to do multi-table joins in MySQL involving composite foreign keys?

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    Britai开发者_StackOverflown   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.

OMG Ponies' code results in each unit being listed twice, once per side, rather than only for the side which its parent nation is on:

[scenID]  [side]   [nation]   [name]
BaBu001     1   America   CAPT
BaBu001     1   America   HMG
BaBu001     1   Germany   CAPT
BaBu001     1   Germany   GREN
BaBu001     2   America   CAPT
BaBu001     2   America   HMG
BaBu001     2   Germany   CAPT
BaBu001     2   Germany   GREN

correct results would be

[scenID]  [side]   [nation]   [name]
BaBu001     1   America   CAPT
BaBu001     1   America   HMG
BaBu001     2   Germany   CAPT
BaBu001     2   Germany   GREN

And to get that we modify the code like so:

SELECT sn.side, snu.scenid, ubd.nation, ubd.unitname
FROM sn
JOIN snu 
        ON snu.scenid=sn.scenid AND snu.scenid = 'scenID'
JOIN ubd
        ON ubd.nation=sn.nation AND ubd.unitid=snu.unitid //double join is the key change
ORDER BY sn.side, ubd.nation, ubd.unitname

If you only want UNIT_BASIC_DATA rows/records with a relationship in the SCENARIO_NEEDUNITS table, use:

  SELECT snu.scenid,
         sn.side,
         ubd.nation,
         ubd.name
    FROM UNIT_BASIC_DATA ubd
    JOIN SCENARIO_NEEDUNITS snu ON snu.unitid = ubd.unitid
                               AND snu.scenid = ?
    JOIN SCENARIO_NATIONS sn ON sn.scenid = snu.scenid
ORDER BY snu.scenid, sn.side, ubd.nation, ubd.name

Replace the ? with whatever scenid you wish to look for.

You don't need to specify ASC - it's the default.