MySQL not returning any result from my select query

Question

I have the following multiple fields where I want to show statement of my expense and income. I am making a query to return description, credit, debit and balance from the MySQL query. SQL Fiddle

CREATE TABLE IF NOT EXISTS `trans` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`debit` decimal(15,4) NOT NULL,
`credit` decimal(15,4) NOT NULL,
`catid` smallint(5) NOT NULL,
`incomeid` bigint(20) unsigned NOT NULL DEFAULT '0',
`expenseid` bigint(20) unsigned NOT NULL DEFAULT '0',
`bankid` int(3) unsigned NOT NULL,
`date` date NOT NULL,
`updated_last` datetime NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;

--
-- Dumping data for table `trans`
--

INSERT INTO `trans` (`ID`, `debit`, `credit`, `catid`, `incomeid`, `expenseid`, `bankid`, `date`, `updated_last`) VALUES
(1, 0.0000, 2078.1000, 23, 1, 0, 2, '2015-04-01', '2015-04-30 14:16:37'),
(2, 0.0000, 2052.8200, 23, 2, 0, 2, '2015-04-02', '2015-04-30 14:17:23'),
(3, 0.0000, 4906.6200, 23, 3, 0, 2, '2015-04-02', '2015-04-30 14:17:06'),
(4, 0.0000, 12360.0500, 23, 4, 0, 1, '2015-04-02', '2015-04-30 12:18:15'),
(5, 0.0000, 10750.0000, 23, 5, 0, 2, '2015-04-03', '2015-04-30 12:25:31'),
(6, 0.0000, 2247.2000, 23, 6, 0, 1, '2015-04-03', '2015-04-30 12:29:45'),
(7, 0.0000, 4775.3000, 23, 7, 0, 2, '2015-04-04', '2015-04-30 12:37:40'),
(8, 0.0000, 2052.8200, 23, 8, 0, 2, '2015-04-04', '2015-04-30 14:16:05'),
(9, 2280.9100, 2280.9100, 23, 0, 1, 2, '2015-04-06', '2015-04-30 14:17:51'),
(10, 0.0000, 25000.0000, 23, 0, 2, 2, '2015-04-04', '2015-04-30 13:46:03');

CREATE TABLE IF NOT EXISTS `expense` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`type` int(11) unsigned NOT NULL,
`date` date NOT NULL,
`itemdesc` varchar(255) NOT NULL,
`quantity` varchar(30) NOT NULL,
`price` decimal(15,4) NOT NULL,
`grandtotal` decimal(15,4) NOT NULL,
`updated_last` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;

--
-- Dumping data for table `expense`
--

INSERT INTO `expense` (`id`, `type`, `date`, `itemdesc`, `quantity`, `price`, `grandtotal`, `updated_last`) VALUES
(1, 30, '2015-04-04', 'INT-CITY CASH DEP CHG INC ST-EC 300315', '1', 50.0000, 56.1800, '2015-05-01 02:35:37'),
(2, 30, '2015-04-04', 'IMPS P2P 562 508513121760#26-03- 300315', '1', 5.0000, 5.6200, '2015-05-01 02:37:18'),
(3, 29, '2015-04-08', 'mobile 1 Bill', '1', 599.0000, 673.0400, '2015-05-01 02:47:01'),
(4, 29, '2015-04-08', 'mobile 2 Bill Mar 2015', '1', 627.0000, 704.5000, '2015-05-01 02:58:08'),
(5, 38, '2015-04-10', 'staff 1 Salary Mar 2015', '1', 22000.0000, 22000.0000, '2015-05-01 03:02:21'),
(6, 38, '2015-04-10', 'staff 2 Salary Mar 2015', '1', 22000.0000, 22000.0000, '2015-05-01 03:03:40'),
(7, 29, '2015-04-11', 'landline 2 March 2015', '1', 388.9000, 436.9700, '2015-05-01 04:13:24'),
(8, 29, '2015-04-11', 'landline Mar 2015', '1', 605.2300, 680.0400, '2015-05-01 04:20:52'),
(9, 29, '2015-04-11', 'pager bill Mar 2015', '1', 591.4000, 664.5000, '2015-05-01 04:23:28'),
(10, 29, '2015-04-11', 'phone bill Mar 2015', '1', 198.7500, 223.3200, '2015-05-01 04:26:01');


CREATE TABLE IF NOT EXISTS `items` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`description` varchar(200) NOT NULL,
`catid` int(5) unsigned NOT NULL,
`status` tinyint(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;

--
-- Dumping data for table `items`
--

INSERT INTO `items` (`id`, `description`, `catid`, `status`) VALUES
(1, 'product 10000', 23, 1),
(2, 'product 50000', 23, 1),
(3, 'product 100000', 23, 1),
(4, 'product 500000', 23, 1),
(5, 'product High 10000', 23, 1),
(6, 'product High 50000', 23, 1),
(7, 'product High 100000', 23, 1),
(8, 'product High 500000', 23, 1),
(9, 'product Normal 10000', 23, 1),
(10, 'product Normal 25000', 23, 1);

CREATE TABLE IF NOT EXISTS `income` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`type` smallint(5) unsigned NOT NULL,
`date` date NOT NULL,
`itemdesc` int(11) unsigned NOT NULL DEFAULT '0',
`quantity` varchar(30) NOT NULL,
`price` decimal(15,4) NOT NULL,
`grandtotal` decimal(15,4) NOT NULL,
`updated_last` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;

--
-- Dumping data for table `income`
--

INSERT INTO `income` (`id`, `type`, `date`, `itemdesc`, `quantity`, `price`, `grandtotal`, `updated_last`) VALUES
(1, 23, '2015-04-01', 18, '10000', 0.1800, 2078.1000, '2015-04-30 14:16:37'),
(2, 23, '2015-04-02', 18, '10000', 0.1800, 2052.8200, '2015-04-30 14:17:23'),
(3, 23, '2015-04-02', 10, '25000', 0.1700, 4906.6200, '2015-04-30 14:17:06'),
(4, 23, '2015-04-02', 12, '100000', 0.1100, 12360.0500, '2015-04-30 12:18:15'),
(5, 23, '2015-04-03', 12, '100000', 0.1075, 10750.0000, '2015-04-30 12:25:31'),
(6, 23, '2015-04-03', 14, '10000', 0.2000, 2247.2000, '2015-04-30 12:29:45'),
(7, 23, '2015-04-04', 10, '25000', 0.1700, 4775.3000, '2015-04-30 12:37:40'),
(8, 23, '2015-04-04', 9, '10000', 0.1800, 2052.8200, '2015-04-30 14:16:05'),
(9, 23, '2015-04-06', 14, '10000', 0.2000, 2280.9100, '2015-04-30 14:17:51'),
(10, 23, '2015-04-04', 53, '250000', 0.1000, 25000.0000, '2015-04-30 13:46:03');

MySQL query:

set @depos=0;
set @total=0;
SELECT A.`date` , A.`debit` , A.`credit` , 
if( A.`credit` >0, @depos := A.`credit`, @depos := @depos + A.`credit` - A.`debit` ) AS depos_bal, 
@total := @total + A.`credit` - A.`debit` AS net_bal, C.`itemdesc`, D.`description` 
FROM `trans` A, `income` B, `expense` C, `items` D 
WHERE A.`expenseid` = C.`id` 
AND A.`incomeid` = B.`id` 
AND B.`itemdesc` = D.`id` 
AND A.`bankid` = '2'
ORDER BY A.`date` ASC, A.`ID` ASC

My several queries are just not good enough to get through with it.

How do I use the query to get all rows like the following?

Date         Description    Debit      Credit         Balance
04-04-2015   Item 1                     4,775.30      27,445.28
04-04-2015   Item 2         56.18       27,389.10
07-04-2015   Item 3                      2,359.56     29,743.04

Answer 1

You have no matching values between your trans, income, and expense tables. So by ANDing "Where A.expenseid = C.id AND A.incomeid = B." will produce no results.

If you run these queries you will realize that:

select * from trans A inner join income B on A.incomeid = B.id;

select * from trans A inner join expense C on A.expenseid = C.id;

Instead of ANDing you should left join on your tables:

SELECT A.`date` , D.`description`, C.`itemdesc`, A.`debit` , A.`credit`, 
if( A.`credit` > 0, @depos := A.`credit`, @depos := @depos + A.`credit` - A.`debit` ) AS depos_bal, 
@total := @total + A.`credit` - A.`debit` AS net_bal
FROM `trans` A left join `income` B ON A.`incomeid` = B.`id` left join `expense` C on A.`expenseid` = C.`id` left join `items` D ON B.`itemdesc` = D.`id`
WHERE A.`bankid` = '2'
ORDER BY A.`date` ASC, A.`ID` ASc;