Hi SF, > I have problem with join and order clause. I have 2 table join with > amount condition and sort by order clause. The result of this join > generate unwanted row to me. > > example > table1 > No. Amount Count > 1 1000 2 > 2 2000 3 > 3 500 1 > table2 > No. Name Amount > 1 A 1000 > 2 B 2000 > 3 C 2000 > 4 D 1000 > 5 E 500 > 6 F 2000 > > sql=select * from table1,table2 where table1.amount=table2.amount order by > table2.amount desc,table2.no > > result table > no name amount count > 2 B 2000 3 > 2 B 2000 3 > 3 C 2000 3 > 3 C 2000 3 > 6 C 2000 3 > 6 C 2000 3 > 1 A 1000 2 > 1 A 1000 2 > 4 D 1000 2 > 4 D 1000 2 > 5 E 500 1 > 5 E 500 1 > > If I did not use order by table2.no in sql it generate result correctly.
There is something missing from your description !!! If the SQL is executed as is, then it will produce six columns of data (three from each table) - but your msg quotes only four. Also, if the SQL is executed it only produces one line of output where two are quoted (and as I understand it, your problem lies). If you are using a script/programming language to produce the output, then the code may be at fault. You do not need the second ORDER BY column, it adds nothing. You do not need ORDER BY at all, to generate the response - only to sequence it. Given that table1 is a summary of table2, it might suit your needs if things are 'forced' to favor table1, eg select * from table1 LEFT JOIN table2 ON table1.amount=table2.amount Add ORDER BY to suit. Regards, =dn --------------------------------------------------------------------- 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
