Andrew Heath Andrew Heath - 3 months ago 12
MySQL Question

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 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.




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

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.

Comments