List:General Discussion« Previous MessageNext Message »
From:Diana Soares Date:January 15 2003 7:49pm
Subject:Re: help w/join condition
View as plain text  
Do you mean that you want all units to appear, even if they have items
not defined (like having 4 defined and the other 5 with no value) ?

If is that, you may try using LEFT JOIN, instead of INNER JOIN.
Columns which are not defined will appear NULL (I did not test this):

SELECT u.name, item1.name AS item1_name,item2.name AS item2_name,
   item3.name AS item3_name,item4.name AS item4_name,
   item5.name AS item5_name,item6.name AS item6_name,item7.name AS
item7_name,
   item8.name AS item8_name,item9.name AS item9_name,item1.item_id as
item1_id,
   item2.item_id as item2_id, item3.item_id as item3_id,item4.item_id as
item4_id,
   item5.item_id as item5_id,item6.item_id as item6_id,
   item7.item_id as item7_id,item8.item_id as item8_id,item9.item_id as
item9_id
FROM units as u 
LEFT JOIN items as item1 ON (u.item1_id = item1.item_id) 
LEFT JOIN items as item2 ON (u.item2_id = item2.item_id) 
....
LEFT JOIN items as item9 ON (u.item9_id = item9.item_id) 
ORDER BY unit_id DESC limit 1;


On Wed, 2003-01-15 at 02:10, mike wrote:
> Hello,
> 
> Is it possible to have a join condition where the condition is not 
> always me? Here is my query:
> 
> SELECT u.name, item1.name AS item1_name,item2.name AS item2_name,
>     item3.name AS item3_name,item4.name AS item4_name,
>     item5.name AS item5_name,item6.name AS item6_name,item7.name AS 
> item7_name,
>     item8.name AS item8_name,item9.name AS item9_name,item1.item_id as 
> item1_id,
>     item2.item_id as item2_id, item3.item_id as item3_id,item4.item_id 
> as item4_id,
>     item5.item_id as item5_id,item6.item_id as item6_id,
>     item7.item_id as item7_id,item8.item_id as item8_id,item9.item_id as 
> item9_id
> FROM items AS item1,items AS item2,items AS item3,items AS item4,items 
> AS item5,
>     items AS item6,items AS item7,items AS item8,items AS item9
> INNER JOIN units as u
>     ON u.item1_id=item1.item_id
>     AND u.item2_id=item2.item_id
>     AND u.item3_id=item3.item_id
>     AND u.item4_id=item4.item_id
>     AND u.item5_id=item5.item_id
>     AND u.item6_id=item6.item_id
>     AND u.item7_id=item7.item_id
>     AND u.item8_id=item8.item_id
>     AND u.item9_id=item9.item_id
> ORDER BY unit_id DESC limit 1;
> 
> This works great as long as each unit record has a item_id in the 
> units_item_id field.  My probelms is
> not all units will have 9 items.  Most units will have less than 9 items.
> 
> Any help would be greatly appreciated,
> Thanks in advance,
> Mike
> 
> 
> 
> ---------------------------------------------------------------------
> Before posting, please check:
>    http://www.mysql.com/manual.php   (the manual)
>    http://lists.mysql.com/           (the list archive)
> 
> To request this thread, e-mail <mysql-thread129970@stripped>
> To unsubscribe, e-mail <mysql-unsubscribe-dsoares=fc.up.pt@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
-- 
Diana Soares

Thread
help w/join conditionmike15 Jan
  • Re: help w/join conditionDiana Soares15 Jan