Hello SQL Gurus I am trying to write an sql statement that will return a row which contains vendor info, how much he owes, total payments and balance due.
The results should look like this : ABC,VENDOR ONE,333.33,199.98,133.35 DEF,VENDOR TWO,333.33, 0.00,333.33 The statement uses 3 tables and contains following data //////////////// VENDORS \\\\\\\\\\\\\\\\\\\\\\ CREATE TABLE vendor ( vendorno varchar(10) NOT NULL, vendorname varchar(30) NOT NULL ); INSERT INTO vendor VALUES( 'ABC', 'VENDOR ABC'); INSERT INTO vendor VALUES( 'DEF', 'VENDOR DEF'); //////////////// INVOICES \\\\\\\\\\\\\\\\\\\\\\ CREATE TABLE invoices ( vendorno varchar(10) NOT NULL, invdate date DEFAULT '0000-00-00' NOT NULL, invoiceno varchar(10) NOT NULL, total decimal(10,2) DEFAULT '0.00' NOT NULL ); INSERT INTO invoices VALUES( 'ABC', '2002-02-01', '101', '111.11'); INSERT INTO invoices VALUES( 'ABC', '2002-02-02', '102', '222.22'); INSERT INTO invoices VALUES( 'DEF', '2002-02-02', '103', '333.33'); //////////////// PAYMENTS \\\\\\\\\\\\\\\\\\\\\\ CREATE TABLE payments ( vendorno varchar(10) NOT NULL, paydate date DEFAULT '0000-00-00' NOT NULL, invoiceno varchar(10) NOT NULL, paid decimal(10,2) DEFAULT '0.00' NOT NULL ); INSERT INTO payments VALUES( 'ABC', '2002-03-01', '101', '55.55'); INSERT INTO payments VALUES( 'ABC', '2002-03-03', '102', '66.66'); INSERT INTO payments VALUES( 'ABC', '2002-03-04', '102', '77.77'); /////// DETAILED SQL STATEMENT \\\\\\\\\\\\\\\\\ I have tried this statement which returns a detailed listing but what I want is a summarized listing select vendor.vendorno,vendor.vendorname,invoices.total, (payments.paid) , (invoices.total-payments.paid) as balance from vendor LEFT JOIN invoices on (vendor.vendorno=invoices.vendorno) LEFT JOIN payments on (vendor.vendorno=payments.vendorno) and (invoices.invoiceno=payments.invoiceno) order by vendor.vendorno ////// SUMMARIZED STATEMENT \\\\\ This summarized listing returns duplicate values for total and balance. select vendor.vendorno,vendor.vendorname,sum(invoices.total), sum(payments.paid) , sum(invoices.total-payments.paid) as balance from vendor LEFT JOIN invoices on (vendor.vendorno=invoices.vendorno) LEFT JOIN payments on (vendor.vendorno=payments.vendorno) and (invoices.invoiceno=payments.invoiceno) group by vendor.vendorno Am using Mysql 3.23.47 NT --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
