Hi,
I have got 2 tables one of the is "customers" the other is "bill". In the
customers table i store my customers informations. In the bill table i store
my billings. if i take a bill bill.direction=0, if i give a bill
bill.direction=1. And now i must take a report Like This..
Customer Code Customer loan
credit total
----------------------------------------------------------------------------
---------------------
10021 Bob 2515$
500$ 2015$
10022 Mark 530$
600$ -70$
10023 Fred 7500$
0 7500$
..........
and its goes on...
Bill Table
-----------
CREATE TABLE `bill` (
`ID` int(11) NOT NULL auto_increment,
`direction` tinyint(4) NOT NULL default '0',
`BillNo` varchar(16) NOT NULL default '',
`BillDate` date default '0000-00-00',
`Customer` int(11) default '0',
`Total` float(10,2) default '0.00'
PRIMARY KEY (`ID`),
UNIQUE KEY `ID` (`ID`),
UNIQUE KEY `BillNo` (`BillNo`),
KEY `BillDate` (`BillDate`),
KEY `Customer` (`Customer`)
) TYPE=MyISAM;
Customer Table
---------------
CREATE TABLE `customer` (
`ID` int(10) unsigned NOT NULL auto_increment,
`Code` varchar(32) NOT NULL default '0',
`Name` varchar(80) default NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `Code` (`Code`),
KEY `Name` (`Name`)
) TYPE=MyISAM;
İ use a query like
------------------
SELECT cus.`Code` AS `Customer Code`,
cus.`Name` AS `Customer`,
IF (bil.`direction`=0,SUM(bil.`Total`), NULL ) AS Loan,
IF (bil.`direction`=1,SUM(bil.`Total`), NULL ) AS Credit
FROM customer AS cus,
bill AS bil
WHERE bil.`Customer` = cus.`ID`
GROUP BY bil.`Customer`
ORDER BY cus.`Name`
But it gives wrong results. My query is to add all Totals, its doesnt look
if direction is 1 or 0. The out put like this
Customer Code Customer loan
credit
----------------------------------------------------------------------------
--------
10021 Bob 3015$
0
10022 Mark 0
1030$
10023 Fred 7500$
0
..........
I must send only one query ! Is there any body can help me???
Aziz Durmaz
---------------------------------------------------------------------
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
