Page 1 of 1

Help with a query

Posted: Sun Aug 15, 2004 1:12 am
by lazy_yogi
Hi, I've got a proabably fairly simple mysql query, but I'm ite sure how to do it.

I have these 2 tables (these are the relavent fields):

Code: Select all

Invoice
--------
id
cost

Payment
----------
invoice
amount
Each Invoice has a number of Payments (could be none).

What I need is to be able to select all invoices and also get an extra column paid_off which shows how much was paid off for each invoice in total.

Can anyone offer some help here?
Cheers.

Posted: Sun Aug 15, 2004 1:36 am
by feyd

Code: Select all

SELECT a.*, SUM(b.amount) `paid_off` FROM Invoice a LEFT JOIN Payment b ON b.invoice = a.id GROUP BY b.invoice
something like that.. I think..

Posted: Sun Aug 15, 2004 7:15 am
by lazy_yogi
Yep, that worked.
Thanks for the help.

Just one more thing. If there are no entries in the payment table for an entry, it shows as 'null' .
Is there a way to have this display as zero?

Posted: Sun Aug 15, 2004 10:38 am
by cdickson
Set 0 as the default in that field of your table.

Posted: Sun Aug 15, 2004 6:42 pm
by lazy_yogi
There is no field 'paid_off'. That's a field that is calculated from another relating table.

Posted: Sun Aug 15, 2004 11:18 pm
by feyd
when you echo it out, cast the paid_off field to (float)..

Posted: Wed Aug 18, 2004 9:09 am
by lazy_yogi
Just wondering if there is a way to do it in the db?

Posted: Wed Aug 18, 2004 11:20 am
by feyd
I believe using an IF/ISNULL control would do it

Posted: Thu Aug 19, 2004 2:59 am
by lazy_yogi
ah awesome .. thanks again

Posted: Fri Aug 20, 2004 10:19 am
by lazy_yogi
There is an odd problem. I have 4 rows in my invoice table.
With this query, I get 3 invoice rows only- one for each of the 3 customers that have invoice/s.

Code: Select all

SELECT *, invoice.* , IF(ISNULL(SUM(payment.cost)), 0.00, SUM(payment.cost)) as paid_off
FROM invoice
  LEFT JOIN payment ON invoice.invoice_id = payment.invoice_id
  LEFT JOIN customer ON invoice.customer_id = customer.customer_id WHERE '1'='1'
GROUP BY payment.invoice_id
ORDER BY invoice.invoice_id
But when I add this to the where clause I get both lines for customer with customer_id as '1'

Code: Select all

"AND customer.customer = '1'"
Any idea why this might be so?
I am trying to get all invoice rows from this query.

Posted: Fri Aug 20, 2004 10:25 am
by feyd
the "where '1'='1'" isn't needed, as for the 3 versus 4, could you post an export of these tables so I can fiddle with it when I get a chance later today?

Posted: Fri Aug 20, 2004 7:49 pm
by lazy_yogi
It's not important to the current problem, but the "where '1'='1'" is so that i can re-use the query by passing in a where clase such as

Code: Select all

function select($where){
    return "SELECT * FROM table WHERE '1'='1'".$where."ORDER BY table_id"
}
function get_by_customer_id($id){
    $sql = select(" AND id='".$id."'")
}
function get_by_invoice_id($id){
    $sql = select("AND invoice_id='".$id."'")
}
Yep, here's the export:

Code: Select all

Database jbat running on localhost 
# phpMyAdmin SQL Dump
# version 2.5.5-pl1
# http://www.phpmyadmin.net
#
# Host: localhost
# Generation Time: Aug 21, 2004 at 10:36 AM
# Server version: 4.0.18
# PHP Version: 5.0.0RC2
# 
# Database : `jbat`
# 

# --------------------------------------------------------

#
# Table structure for table `customer`
#

CREATE TABLE `customer` (
  `customer_id` int(11) NOT NULL auto_increment,
  `first_name` varchar(32) NOT NULL default '',
  `middle_name` varchar(32) NOT NULL default '',
  `last_name` varchar(32) NOT NULL default '',
  `address_number` varchar(8) NOT NULL default '',
  `address_street` varchar(32) NOT NULL default '',
  `address_suburb` varchar(32) NOT NULL default '',
  `address_state` char(3) NOT NULL default '',
  `address_postcode` varchar(4) NOT NULL default '',
  `date_of_birth` varchar(10) NOT NULL default '',
  `sex` enum('Male','Female') NOT NULL default 'Male',
  `email` varchar(64) NOT NULL default '',
  `fax_number` varchar(16) NOT NULL default '',
  `home_phone_number` varchar(16) NOT NULL default '',
  `work_phone_number` varchar(16) NOT NULL default '',
  `mobile_number` varchar(16) NOT NULL default '',
  `notes` text NOT NULL,
  PRIMARY KEY  (`customer_id`)
) TYPE=MyISAM AUTO_INCREMENT=8 ;

#
# Dumping data for table `customer`
#

INSERT INTO `customer` VALUES (1, 'JohnA', 'M.', 'Doe', '22', 'Some St.', 'Kingsford', 'NSW', '1234', '1980-01-06', 'Male', 'none@none.com', '', '99996666', '', '0404040404', 'cazy dude');
INSERT INTO `customer` VALUES (2, 'JohnB', 'M.', 'Doe', '22', 'Some St.', 'Kingsford', 'NSW', '1234', '1980-01-06', 'Male', 'none@none.com', '', '99996666', '', '0404040404', 'cazy dude');
INSERT INTO `customer` VALUES (3, 'JohnC', 'M.', 'Doe', '22', 'Some St.', 'Kingsford', 'NSW', '1234', '1980-01-06', 'Male', 'none@none.com', '', '99996666', '', '0404040404', 'cazy dude');
INSERT INTO `customer` VALUES (4, 'JohnD', 'M.', 'Doe', '22', 'Some St.', 'Kingsford', 'NSW', '1234', '1980-01-06', 'Male', 'none@none.com', '', '99996666', '', '0404040404', 'cazy dude');
INSERT INTO `customer` VALUES (5, 'JohnE', 'M.', 'Doe', '22', 'Some St.', 'Kingsford', 'NSW', '1234', '1980-01-06', 'Male', 'none@none.com', '', '99996666', '', '0404040404', 'cazy dude');
INSERT INTO `customer` VALUES (6, 'JohnF', 'M.', 'Doe', '22', 'Some St.', 'Kingsford', 'NSW', '1234', '1980-01-06', 'Male', 'none@none.com', '', '99996666', '', '0404040404', 'cazy dude');
INSERT INTO `customer` VALUES (7, 'JohnG', 'M.', 'Doe', '22', 'Some St.', 'Kingsford', 'NSW', '1234', '1980-01-06', 'Male', 'none@none.com', '', '99996666', '', '0404040404', 'cazy dude');

# --------------------------------------------------------

#
# Table structure for table `invoice`
#

CREATE TABLE `invoice` (
  `invoice_id` int(11) NOT NULL auto_increment,
  `customer_id` int(11) NOT NULL default '0',
  `cost` decimal(10,2) NOT NULL default '0.00',
  `discount_reason` text NOT NULL,
  PRIMARY KEY  (`invoice_id`)
) TYPE=MyISAM AUTO_INCREMENT=5 ;

#
# Dumping data for table `invoice`
#

INSERT INTO `invoice` VALUES (1, 1, '61.00', '');
INSERT INTO `invoice` VALUES (2, 2, '60.00', '');
INSERT INTO `invoice` VALUES (3, 3, '60.00', '');
INSERT INTO `invoice` VALUES (4, 1, '50.00', '');

# --------------------------------------------------------

#
# Table structure for table `payment`
#

CREATE TABLE `payment` (
  `payment_id` int(11) NOT NULL auto_increment,
  `invoice_id` int(11) NOT NULL default '0',
  `payment_type` enum('Cash','Cheque','Credit') NOT NULL default 'Cash',
  `cost` decimal(10,2) NOT NULL default '0.00',
  `date` varchar(10) NOT NULL default '',
  PRIMARY KEY  (`payment_id`)
) TYPE=MyISAM AUTO_INCREMENT=5 ;

#
# Dumping data for table `payment`
#

INSERT INTO `payment` VALUES (1, 1, 'Cash', '61.00', '2004-04-04');
INSERT INTO `payment` VALUES (2, 2, 'Cash', '20.00', '2004-04-06');
INSERT INTO `payment` VALUES (3, 2, 'Cash', '20.00', '2004-04-06');

Posted: Sun Aug 22, 2004 10:03 am
by feyd

Code: Select all

SELECT a.*, IF(ISNULL(SUM(b.cost)), 0.00, SUM(b.cost)) `paid_off`, (a.cost - IF(ISNULL(SUM(b.cost)), 0.00, SUM(b.cost))) `balance`, c.*
FROM invoice a
LEFT JOIN payment b ON a.invoice_id = b.invoice_id
INNER JOIN customer c ON a.customer_id = c.customer_id
GROUP BY a.invoice_id

Posted: Tue Aug 24, 2004 7:26 am
by lazy_yogi
Thanks very much for your help. Works perfectly.