Join problems: values from different rows...

[Stefan van Roosmalen]

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.

[Rich Ryan]

"Stefan van Roosmalen" <ro******@planet.nl> wrote in message
news:44**********************@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

[Stefan van Roosmalen]

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" <rr***@cshore.com> wrote in message
news:n3*******************@newssvr13.news.prodigy. com...

"Stefan van Roosmalen" <ro******@planet.nl> wrote in message
news:44**********************@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