470,604 Members | 2,226 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,604 developers. It's quick & easy.

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.
Jun 2 '06 #1
2 1405

"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
Jun 2 '06 #2
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

Jun 2 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by jain-neeraj | last post: by
7 posts views Thread by Dave | last post: by
2 posts views Thread by Martin | last post: by
3 posts views Thread by don | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.