help w/join condition

[mike]

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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php