Join three tables with SUM

[egf]

I have three tables, a purchase order table, an items ordered table, and an invoice table. I want to select records from the purchase orders based on various criteria (eg vendor, or date, etc) and then get the SUM of the items ordered from the second table, and the SUM of the invoices (if any exist yet) from the third table. Is this possible with a single select statement?

Here is one of my trial queries that seems to be close to what I am looking for. Depending on how I apply (phrase) the 'group by' clause and the table being grouped, it sometimes produces corect results for one of the sums or the other:

SELECT po.ponum, po.poDate, po.vendor, po.status,
SUM(item.qty*item.cost) AS itemtotal, SUM(invoice.cost) AS invoicetotal
FROM po LEFT JOIN item ON po.ponum=item.ponum
LEFT JOIN invoice ON po.ponum=poinvoice.ponum
WHERE po.status='open' AND
((po.pon BETWEEN $poNumLo AND $poNumHi) OR (po.poDate BETWEEN '$poDateLo' AND '$poDateHi'))
GROUP BY item.pon

The actual application and tables are a bit more complex, but I simplified it here for clarity.

Thanks for any advice!

 

[r937]

select ponum
     , poDate
     , vendor
     , status
     , ( select sum(qty*cost) 
           from item
          where ponum = po.ponum
        ) as itemtotal
     , ( select sum(cost) 
           from invoice
          where ponum = po.ponum
        ) as invoicetotal
  from po 
 where status = 'open' 
   and (
       pon between $poNumLo and $poNumHi
    or poDate between '$poDateLo' and '$poDateHi'
       )

r937.com | rudy.ca

 

[gg67]

hello,

i have the same problem.

original :

SELECT mbr.ID,mbr.Name AS Member,
SUM(sale.Qy) AS Pieces,SUM(sale.Price*sale.Qy) AS Total,
SUM(pay.Price) AS Deposit
FROM tbl_member AS mbr INNER JOIN tbl_cd_sale AS sale
ON mbr.ID=sale.Member
LEFT JOIN tbl_cd_payment AS pay ON pay.Member=mbr.ID
WHERE (sale._deleted=0) AND (sale.Type=1)
GROUP BY sale.Member;

and this won't works too:

SELECT mbr.ID,mbr.Name AS Member,
SUM(sale.Qy) AS Pieces,SUM(sale.Price*sale.Qy) AS Total,
(
SELECT SUM(Price) FROM tbl_cd_payment
WHERE Member=mbr.ID
) AS Deposit
FROM tbl_member AS mbr INNER JOIN tbl_cd_sale AS sale
ON mbr.ID=sale.Member
WHERE (sale._deleted=0) AND (sale.Type=1)
GROUP BY mbr.ID;

i got this error:
syntax error near 'SELECT SUM(Price) FROM tbl_cd_payment WHERE Member=mbr.ID) AS De' @ ligne 1

thx in advance

(i use mysql v4.0.15)

 

[r937]

cannot use subqueries before version 4.1

by the way, 4.1 is almost 2 years ago

r937.com | rudy.ca

 

[gg67]

Thank u very much

 

[egf]

After getting my MySQL installation up to snuff the subqueries work fine. Thanks for the suggestion. Out of curiosity I am still looking into the correct 'join' syntax.