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
