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