Help with a query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
lazy_yogi
Forum Contributor
Posts: 243
Joined: Fri Jan 24, 2003 3:27 am

Help with a query

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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..
User avatar
lazy_yogi
Forum Contributor
Posts: 243
Joined: Fri Jan 24, 2003 3:27 am

Post 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?
cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

Post by cdickson »

Set 0 as the default in that field of your table.
User avatar
lazy_yogi
Forum Contributor
Posts: 243
Joined: Fri Jan 24, 2003 3:27 am

Post by lazy_yogi »

There is no field 'paid_off'. That's a field that is calculated from another relating table.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

when you echo it out, cast the paid_off field to (float)..
User avatar
lazy_yogi
Forum Contributor
Posts: 243
Joined: Fri Jan 24, 2003 3:27 am

Post by lazy_yogi »

Just wondering if there is a way to do it in the db?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I believe using an IF/ISNULL control would do it
User avatar
lazy_yogi
Forum Contributor
Posts: 243
Joined: Fri Jan 24, 2003 3:27 am

Post by lazy_yogi »

ah awesome .. thanks again
User avatar
lazy_yogi
Forum Contributor
Posts: 243
Joined: Fri Jan 24, 2003 3:27 am

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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?
User avatar
lazy_yogi
Forum Contributor
Posts: 243
Joined: Fri Jan 24, 2003 3:27 am

Post 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');
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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
User avatar
lazy_yogi
Forum Contributor
Posts: 243
Joined: Fri Jan 24, 2003 3:27 am

Post by lazy_yogi »

Thanks very much for your help. Works perfectly.
Post Reply