MySQL join headaches, please help!

Question

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.

:-/

---

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

Answer 1

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.