Incorrect query result onINNER JOINS over three tables

Joe Smith IX

Hi, I have 3 tables below. How can I query total payment and prod_principal group by date? This is what I have, but it results incorrect, why? Help please, anyone? Thanks.

SELECT DAY(shift.date) AS day1,
MONTH(shift.date) AS month1,
YEAR(shift.date) AS year1,
SUM(invoice.payment) AS sum1,
SUM(invoice_details.prod_principal) AS sum2
FROM shift INNER JOIN
invoice ON shift.id = invoice.shift_id INNER JOIN
invoice_details ON invoice.id = invoice_details.invoice_id
GROUP BY DAY(shift.date), MONTH(shift.date), YEAR(shift.date)
ORDER BY year1, month1, day1

shift invoice invoice_details

id id invoice_id
date shift_id prod_id
payment prod_principal

Arun Immanuel

Try this: select DAY(A.date) AS day1, MONTH(A.date) AS month1, YEAR(A.date) AS year1, SUM(A.payment) AS sum1, SUM(A.prod_principal) AS sum2 From (select * from shift,invoice,invoice_details where shift.id=invoice.shift_id and invoice.id=invoice_details.invoice_id ) A Group by DAY(A.date), MONTH(A.date), YEAR(A.date) ORDER BY year1, month1, day1

Regards, Arun Kumar.A

Joe Smith IX

I tried it, but the result is exactly the same as my original query. the reason i know it's wrong is because sum1 is not the same as queried from the following:

SELECT DAY(shift.date) AS day1,
MONTH(shift.date) AS month1,
YEAR(shift.date) AS year1,
SUM(invoice.payment) AS sum1
FROM shift, invoice
WHERE shift.id = invoice.shift_id
GROUP BY DAY(shift.date),
MONTH(shift.date),
YEAR(shift.date)
ORDER BY year1, month1, day1

maybe i should specify these: - there are many invoices in one shift (having the same shift_id) - there are many invoice_details in one invoice (having the same invoice_id) Please help, thanks.

Arun Immanuel

I do not know much about invoice. Can U provide 4(or minumum) rows of data for each table and the output U exactly need. May be some other person, who know the solution but cannot understand Ur need will help U.

Regards, Arun Kumar.A

Joe Smith IX

here are some examples. i hope it's enough. thanks.

shift: id date
-- ----------
1 05/01/2007
2 05/02/2007

invoice: id shift_id payment
-- -------- -------
1 1 100
2 1 250
3 2 375
4 2 210
5 2 333

invoice_details: invoice_id prod_id prod_principal
---------- ------- --------------
1 101 30
1 156 55
2 258 180
3 147 100
3 268 225
4 251 60
4 369 35
4 158 88
5 125 100
5 395 50
5 158 65
5 228 33
5 358 20

the query i want:

day1 month1 year1 sum1 sum2

1 5 2007 350 265
2 5 2007 918 776

note: 350 = 100+250 (total payment on 05/01/2007)
918 = 375+210+333 (total payment on 05/02/2007)
265 = 30+55+180 (total prod_principal on 05/01/2007)
776 = 100+...+20 (total prod_principal on 05/02/2007)

Arun Immanuel · shift: id date -- ---------- 1 05/01/2007 2 05/02/2007

Use this for the moment, untill you find better solution.

SELECT DAY(shift.date) AS day1,
MONTH(shift.date) AS month1,
YEAR(shift.date) AS year1,
C.SumPay AS sum1,
C.SumPri AS Sum2
FROM shift ,

(select shift_id,sum(payment) "SumPay" ,sum(A.sumPrincipal) "SumPri" from invoice B,

(select invoice_id,sum(prod_principal) "sumPrincipal" from invoice_details group by invoice_id) A

where B.id=A.invoice_id group by B.shift_id) C

where shift.id=C.shift_id

Regards, Arun Kumar.A

Joe Smith IX

thanks a lot, you've saved me hours of stressing out :)