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