LEFT OUTER JOINS

Client Joined: Oct 07, 2003 Posts: 735 Location: Ohio

I am trying to get members to fill my roster and have tried several variations of this but can't get it right.

I want a member to appear on the roster whether or not they have a match in the rank table or weapon table. They must have a unit_id assigned.

Code:

$sql = "SELECT * FROM " . $prefix . "_milpacs_members mm LEFT OUTER JOIN (" . $prefix . "_milpacs_weapons mw) LEFT OUTER JOIN (" . $prefix . "_milpacs_ranks mr) JOIN (" . $prefix . "_milpacs_units mu) ON (mu.unit_id = mm.unit_id AND mw.weapon_id = mm.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";

The reaon for this is if a weapon or rank is deleted I still want the members to appear on the roster so I may go in and edit and set him to another rank or weapon.
[ Only registered users can see links on this board! Get registered or login! ]

Posted: Tue Jun 13, 2006 9:56 pm

See if this helps you here:
[ Only registered users can see links on this board! Get registered or login! ]

Posted: Wed Jun 14, 2006 8:08 am

But I'm joining 4 different tables together.

milpacs_members
milpacs_weapons
milpacs_units
milpacs_ranks

They must belong to a unit to show on the roster, but need not be assigned a rank or weapon.

Posted: Wed Jun 14, 2006 9:15 am

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";

Posted: Wed Jun 14, 2006 12:15 pm

Outstanding...

Posted: Wed Jun 14, 2006 12:18 pm

One more quick question.

If there is no weapon assigned and my roster has a hyperlink to the weapon profile such as:

Code:

<a class="content" href="modules.php?name=<?php echo $module_name ?>&amp;file=weaponprofile&amp;weapon_id=<?php echo $weapon_id ?>"><?php echo $make ?></a>

How could I make it not hyperlinked if I went this route?

Code:

if ($row["weapon_id"] =="") {


      $make = "Not Assigned"; 


   } else {


      $make = "$make";


   }

You follow???

Check it out here.
[ Only registered users can see links on this board! Get registered or login! ]

Posted: Fri Jun 23, 2006 4:29 pm

Code:

if ($row["weapon_id"]) {


    $make = "<a class=\"content\" href=\"modules.php?name=$module_name&amp;file=weaponprofile&amp;weapon_id={$row['weapon_id']}\">$make</a>";


} else {


    $make = "Not Assigned";


}

Posted: Fri Jun 30, 2006 10:03 am

Thanks dj

But I put this in and it doesn't seem to do what is desired.

Here is the block of code in question in my roster.

Code:

<td width='15%' align='center'><?php echo $ri ?></td><td width='30%'>&nbsp;<a class="content" href="modules.php?name=<?php echo $module_name ?>&amp;file=soldierprofile&amp;uniqueid=<?php echo $uniqueid ?>"><?php echo $u_name ?></a>


   </td><td align='center' width='25%'><a class="content" href="modules.php?name=<?php echo $module_name ?>&amp;file=unitprofile&amp;unit_id=<?php echo $unit_id ?>"><?php echo $unitname ?></a></td><td width='25%' align='center'><?php echo $position ?></td><td width='14%' align='center'><a class="content" href="modules.php?name=<?php echo $module_name ?>&amp;file=weaponprofile&amp;weapon_id=<?php echo $weapon_id ?>"><?php echo $make ?></a></td><td width='8%' align='center'><?php echo $status ?></td>

You can see the following

Code:

><a class="content" href="modules.php?name=<?php echo $module_name ?>&amp;file=weaponprofile&amp;weapon_id=<?php echo $weapon_id ?>"><?php echo $make ?></a>

is where the weapon is hyperlinked.

Setting this doesn't effect the way $make is displayed, unless I am missing soemthing here.

Code:

if ($row["weapon_id"]) { 


    $make = "<a class=\"content\" href=\"modules.php?name=$module_name&amp;file=weaponprofile&amp;weapon_id={$row['weapon_id']}\">$make</a>"; 


   } else { 


    $make = "Not Assigned"; 


   }