Hi, At 05:35 PM 15/11/2001 -0800, you wrote: >I'm having a problem sorting records that are used in a left join statement. >Basically what happens is that any record that is not in table1 doesn't get >sorted correctly. It first sorts all the records that have valid 'c2' >records in both table1 and table2, then moves on to all the records that >only have valid 'c2' records in table2. > >Is there any way to make the ORDER BY portion of the statement insert a '0' >value for records that do not have a table1.c2 value. > >Here is the a simplified SQL statement that illustrates my point. > > >SELECT * FROM table2 LEFT JOIN table1 USING (c1) ORDER BY (table2.c2 - >table1.c2)
If I read you right I feel you are looking for this qurey: SELECT * FROM table2 LEFT JOIN table1 USING (c1) ORDER BY (table2.c2 - ifnull(table1.c2,0)) Note Any mathematical operation with a null value yields null. Anvar. --------------------------------------------------------------------- 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
