Join problems: values from different rows...

Hi there,

I have Orders and Payments. Sometimes the Orders do not have Payments. So, I
decided to use LEFT JOIN.
Furthermore, some Orders have 1 related Payment, and some Orders have 2 or
even more related Payments.
Now, I want to combine the sum of all Payments and the last Payment in one
row.

select Orders.SeqNr,
sum(B1.Amount) Total_paid,
max(B2.SeqNr) Last_record,
B2.Amount Last_amount
from Orders
left join Payments B1
on B1.FK_Orders = Orders.SeqNr
left join Payments B2
on B2.FK_Orders = Orders.SeqNr
group by Orders.SeqNr

If there is only 1 payment per order, then I will see perfect results.
However, if there is more than 1 payment per order, then the results are
wrong...
The column Last_amount does not contain the amount which belongs to the
column Last_record.

I have tried everything.........

Please help me out here.

MySQL version = 4.0.

Regards,
Stefan.

Re: Join problems: values from different rows...

"Stefan van Roosmalen" wrote in message
news:448060a2$0$2030$ba620dc5@text.nova.planet.nl...
> Hi there,
>
> I have Orders and Payments. Sometimes the Orders do not have Payments. So,
I
> decided to use LEFT JOIN.
> Furthermore, some Orders have 1 related Payment, and some Orders have 2 or
> even more related Payments.
> Now, I want to combine the sum of all Payments and the last Payment in one
> row.
>
> select Orders.SeqNr,
> sum(B1.Amount) Total_paid,
> max(B2.SeqNr) Last_record,
> B2.Amount Last_amount
> from Orders
> left join Payments B1
> on B1.FK_Orders = Orders.SeqNr
> left join Payments B2
> on B2.FK_Orders = Orders.SeqNr
> group by Orders.SeqNr
>
> If there is only 1 payment per order, then I will see perfect results.
> However, if there is more than 1 payment per order, then the results are
> wrong...
> The column Last_amount does not contain the amount which belongs to the
> column Last_record.
>
> I have tried everything.........
>
> Please help me out here.
>
> MySQL version = 4.0.
>
> Regards,
> Stefan.
>
Please post your CREATE TABLE statements for the two tables. It's tough to
figure out what you mean by the sequence numbers and "last payment".

Thanks,
Rich

Re: Join problems: values from different rows...

This is the create statement for both tables I have used:

CREATE TABLE `orders` (
`SeqNr` int(11) NOT NULL auto_increment,
`OrderNo` int(11) NOT NULL default '0',
`Date` date NOT NULL default '0000-00-00',
`Time` time NOT NULL default '00:00:00',
PRIMARY KEY (`SeqNr`)
) TYPE=MyISAM;

CREATE TABLE `payments` (
`SeqNr` int(11) NOT NULL auto_increment,
`FK_Order` int(11) NOT NULL default '0',
`Amount` double(10,2) NOT NULL default '0.00',
PRIMARY KEY (`SeqNr`)
) TYPE=MyISAM;

Note:
FK_Order is the Foreign Key to table Orders.
SeqNr is just the AUTO ID



"Rich Ryan" wrote in message
news:n32gg.90522$H71.80743@newssvr13.news.prodigy.com...
>
> "Stefan van Roosmalen" wrote in message
> news:448060a2$0$2030$ba620dc5@text.nova.planet.nl...
>> Hi there,
>>
>> I have Orders and Payments. Sometimes the Orders do not have Payments.
>> So,
> I
>> decided to use LEFT JOIN.
>> Furthermore, some Orders have 1 related Payment, and some Orders have 2
>> or
>> even more related Payments.
>> Now, I want to combine the sum of all Payments and the last Payment in
>> one
>> row.
>>
>> select Orders.SeqNr,
>> sum(B1.Amount) Total_paid,
>> max(B2.SeqNr) Last_record,
>> B2.Amount Last_amount
>> from Orders
>> left join Payments B1
>> on B1.FK_Orders = Orders.SeqNr
>> left join Payments B2
>> on B2.FK_Orders = Orders.SeqNr
>> group by Orders.SeqNr
>>
>> If there is only 1 payment per order, then I will see perfect results.
>> However, if there is more than 1 payment per order, then the results are
>> wrong...
>> The column Last_amount does not contain the amount which belongs to the
>> column Last_record.
>>
>> I have tried everything.........
>>
>> Please help me out here.
>>
>> MySQL version = 4.0.
>>
>> Regards,
>> Stefan.
>>
> Please post your CREATE TABLE statements for the two tables. It's tough to
> figure out what you mean by the sequence numbers and "last payment".
>
> Thanks,
> Rich
>
>