MySQL statement that combines a sub-query and a JOIN is not working
SELECT i.id AS id, i.modify_date as modify_date, s.subscription as subscriptionid, p.paid/i.total AS paidratio
FROM invoices i,
(SELECT p.invoice, sum(amount) AS paid FROM payments p GROUP BY p.invoice) p
LEFT JOIN sub_to_inv s
ON i.id=s.invoice
WHERE p.invoice=i.id
AND i.corporation='3'
AND i.payer=1
The error I get is "unknown column on i.id" which is total bogus - invoices (i) has an id row for sure. They开发者_JAVA技巧 all do.
The purpose of the sub=query is to find out how much of the invoice has been paid. For an invoice that has a "total" column of 1000.00, for example, could have 2 or 3 split payments. What I ultimately wnat to do here is list all the unpaid invoices or partially invoices first. But before I even get to the ORDER BY stage, I need to figure out this error.
Use JOIN
syntax for all joins. Don't mix JOIN
syntax with the comma-style SQL-89 syntax.
SELECT ...
FROM invoices i
INNER JOIN (SELECT...) p
ON p.invoice=i.id
LEFT OUTER JOIN sub_to_inv s
ON i.id=s.invoice
WHERE
AND i.corporation='3'
AND i.payer=1
Explanation: JOIN
has higher precedence than comma-joins. So p JOIN s
is evaluated before the query evaluates the join to i
. Therefore, in the clause ON i.id=s.invoice
, the i
table is not yet known and is an invalid reference.
See http://dev.mysql.com/doc/refman/5.1/en/join.html, in the doc following "Join Processing Changes in MySQL 5.0.12".
Can you try this ?
SELECT i.id AS id, i.modify_date as modify_date, s.subscription as subscriptionid, p.paid/i.total AS paidratio
FROM invoices i
LEFT JOIN
(SELECT p.invoice, sum(amount) AS paid FROM payments p GROUP BY p.invoice) p
ON p.invoice=i.id
LEFT JOIN sub_to_inv s
ON i.id=s.invoice
WHERE i.corporation='3'
AND i.payer=1
I think you might be running into issues because of the order of your table joins in your SQL. Have you tried using an inner join. Perhaps try:
SELECT
i.id AS id,
i.modify_date as modify_date,
s.subscription as subscriptionid,
p.paid/i.total AS paidratio
FROM
invoices i
INNER JOIN
(SELECT
p.invoice,
sum(amount) AS paid
FROM
payments p
GROUP BY
p.invoice) p
ON
p.invoice=i.id
LEFT JOIN
sub_to_inv s
ON
i.id=s.invoice
WHERE
i.corporation='3' AND i.payer=1
精彩评论