How do I use multiple Join Lefts?

[Far Cry]

Here are the tables.

-- Table structure for table `items`

--

CREATE TABLE IF NOT EXISTS `items` (

`item_id` bigint(20) NOT NULL auto_increment,

`item_name` varchar(255) NOT NULL,

`item_description` text NOT NULL,

`item_type` varchar(25) NOT NULL,

`item_cost` double NOT NULL,

`item_sell_value` double NOT NULL,

PRIMARY KEY (`item_id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--

-- Table structure for table `users`

--

CREATE TABLE IF NOT EXISTS `users` (

`userid` bigint(20) NOT NULL auto_increment,

`username` varchar(50) NOT NULL,

`password` varchar(255) NOT NULL,

`email` varchar(75) NOT NULL,

`userlevel` varchar(50) NOT NULL,

`register_date` datetime NOT NULL,

`last_login` datetime NOT NULL,

`total_logins` bigint(20) NOT NULL,

`ip` varchar(50) NOT NULL,

`locked` tinyint(1) NOT NULL,

PRIMARY KEY (`userid`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--

-- Table structure for table `user_equipped_items`

--

CREATE TABLE IF NOT EXISTS `user_equipped_items` (

`userid` bigint(20) NOT NULL,

`primary_id` bigint(20) NOT NULL default '0',

`secondary_id` bigint(20) NOT NULL default '0',

`melee_id` bigint(20) NOT NULL default '0',

PRIMARY KEY (`userid`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--

-- Table structure for table `user_items`

--

CREATE TABLE IF NOT EXISTS `user_items` (

`userid` bigint(20) NOT NULL,

`item_id` bigint(20) NOT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

 

PHP Code:

<?php

$query = mysql_query("SELECT * FROM items LEFT JOIN user_items ON

items.item_id=user_items.item_id WHERE user_items.userid='$userid'");

$numrows = mysql_num_rows($query);

if($numrows > 0){

while($row = mysql_fetch_assoc($query)){

$name = $row['item_name'];

$id= $row['item_id'];

$type = $row['item_type'];

$cost = $row['item_cost'];

$sell_value = $row['item_sell_value'];

?>

<tr>

<td><?php echo"$name";?></td>

<td><?php echo"$type";?></td>

<td><?php echo money_format('%(#10n', $cost);?></td>

<td><?php echo money_format('%(#10n', $sell_value);?></td> My question is, how do I join the user_equipped_items table into the query so I can exclude all items that the user currently has equipped from the while loop?

 

[o3d]

I'm not sure if this will work, but have a look at the query below, I added the "AND items..." section:

 

SELECT * FROM items LEFT JOIN user_items ON

items.item_id=user_items.item_id WHERE user_items.userid='$userid'

AND items.item_id NOT IN (

SELECT primary_id FROM user_equipped_items WHERE userid='$userid'

)

 

This will exclude all item_id's that is found in the user_equipped_items table. I'm also not sure if primary_id is the correct column?

 

To come back to your original question on how to join, it gets complicated when you left join and then want to join another table to that table. Let me know if the above sql works, if not I'll rethink the query.

[Psycho]

Hmm, I don't think this is going to be an easy query because of the way you created your tables. It looks like the `user_equipped_items` contains three separate columns that can contain an item_id. If instead you has a separate record in the `user_equipped_items` for each weapon type (i.e. column for user id, item_id and "type" id to determine whether primary, secondary, melee) then this would be fairly simple.

 

o3d's solution is one possibility, but it only takes into consideration the primary weapon and not the secondary or melee.

 

Give this a try:

SELECT * FROM `items`
LEFT JOIN `user_items`
    USING(`item_id`)
LEFT JOIN `user_equipped_items`
    USING (`userid`)
WHERE `user_items`.`userid` = '$userid'
  AND `user_items`.`item_id` <> `user_equipped_items`.`primary_id`
  AND `user_items`.`item_id` <> `user_equipped_items`.`secondary_id`
  AND `user_items`.`item_id` <> `user_equipped_items`.`melee_id`

[Far Cry]

Works perfectly! You have NO idea how much of a help you've been, I litterally got a headache from trying to figure this out!

[gizmola]

You should seriously consider mjdamato's advice in restructuring your user_equiped_items table.  What you have implemented there is a "repeating group" with the primary_id, secondary_id, melee_id.  You could normalize that table and gain a lot more flexibility of design and ease of querying at a minimal cost.  You would simply need to add an equip_type table that describes the equptypes (primary, secondary, melee, etc)  and then you create a row per item that is equipped.  Makes your joins simple and you don't have to do helacious queries liket he one mj cooked up for you.  Additionally when you add a new equipment slot, instead of modifying the database structure you add a new equiptype and you're off and running.