simple left-join doesn't work. error with: Unknown column
Hi guys,
can anyone assist on this simple query:
SELECT D.* , SUM( TD.debit_amount), SUM(TAP.amount)
FROM debit AS D, transaction_debit AS TD
LEFT JOIN TA_PAYMENTS AS TAP ON D.debit_id=TAP.debit_id
WHERE D.debit_id = TD.debit_id
GROUP BY TD.debit_id
The error message I receive is:
#1054 - Unknown column'D.debit_id' in'on clause'
I'm trying to relate 3 tables (debit, transactions-debit and ta_payment), some payment may not exist so therefore i use left join.
thanks for any pointers
# 1
> > SELECT D.* , SUM( TD.debit_amount), SUM(TAP.amount)
> FROM debit AS D, transaction_debit AS TD
> LEFT JOIN TA_PAYMENTS AS TAP ON
> D.debit_id=TAP.debit_id
> WHERE D.debit_id = TD.debit_id
> GROUP BY TD.debit_id
>
> I'm trying to relate 3 tables (debit,
> transactions-debit and ta_payment), some payment may
> not exist so therefore i use left join.
You are trying to mix "the older join syntax", with the join condition in the where-clause, with "the new join syntax", with the join condition in the "on-clause", which I think is causing trouble.
How about using only "the new syntax":
SELECT D.* , SUM( TD.debit_amount), SUM(TAP.amount)
FROM debit AS D JOIN transaction_debit AS TD on (D.debit_id = TD.debit_id)
LEFT JOIN TA_PAYMENTS AS TAP ON (D.debit_id=TAP.debit_id)
GROUP BY TD.debit_id
Does that help?
# 2
thanks for your reply itupjalu,
I'm getting the result but they are incorrect.
I managed to solved it like this:
SELECT D.*, TAP.totalPay, TD.totalDebt
FROM debit AS D
LEFT JOIN (SELECT SUM(Amount) AS totalPay, debit_id FROM TA_Payments GROUP BY debit_id ) as TAP ON (TAP.debit_id=D.debit_id)
LEFT JOIN (SELECT SUM(debit_amount) AS totalDebt, debit_id FROM transaction_debit GROUP BY debit_id ) AS TD ON (TD.debit_id=D.debit_id)
