LEFT JOIN multiple tables

SFDonovan

Hey everyone,

I have been to this site countless times reading the forums getting my questions answered and it is much appreciated.

One question I have is about LEFT JOINS.

I have a roster for a module in PHPNuke I did called MILPACS. The development site is located here.

http://milpacs.3rd-infantry-division.net/index.php

Here is the roster itself.

http://milpacs.3rd-infantry-division.net/modules.php?name=MILPACS&file=roster

A members must be assigned to a unit to appear in the roster, but they need not be assigned a weapon_id or a rank_id. I can accomplish this with LEFT JOINS but I have four different tables with this query.

milpacs_members
milpacs_weapons
milpacs_units
milpacs_ranks

Here is my code:

$sql = "SELECT * FROM " . $prefix . "_milpacs_members mm 
LEFT JOIN " . $prefix . "_milpacs_weapons mw,
" . $prefix . "_milpacs_units mu,
" . $prefix . "_milpacs_ranks mr
ON mm.unit_id = mu.unit_id 
AND mm.weapon_id = mw.weapon_id 
AND mm.rank_id = mr.rank_id 
AND mm.status IN ('Active','LOA') 
ORDER BY mu.unit_order, mm.subunit_id, mr.rank_order, mm.promotion_dt asc";

Thanks in advance.

leatherback

two points of attention:

what is your question / problem with the code
Please add linebreacks in your code, so it shows in a bit larger text area

SFDonovan

My problem is it doesn't work. I can't figure out the syntax for mulitple joins while using a LEFT JOIN to display all members assigned to a unit regardless if they have a wepon_id or rank_id assigned.

SFDonovan

$sql = "SELECT * FROM " . $prefix . "_milpacs_members mm 
INNER JOIN (" . $prefix . "_milpacs_units mu) ON (mu.unit_id = mm.unit_id) 
LEFT JOIN " . $prefix . "_milpacs_weapons mw ON (mw.weapon_id = mm.weapon_id) 
LEFT JOIN " . $prefix . "_milpacs_ranks mr ON (mr.rank_id = mm.rank_id) 
WHERE mm.status IN ('Active','LOA') 
ORDER BY mu.unit_order, mm.subunit_id, mr.rank_order, mm.promotion_dt asc";