开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜