MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:DL Neil Date:January 26 2002 4:13pm
Subject:Re: join and order by problem
View as plain text  
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


Thread
join and order by problemSommai Fongnamthip25 Jan
  • Re: join and order by problemDL Neil25 Jan