Wazan Wazan - 1 month ago 7
MySQL Question

MYSQL LEFT JOIN result not giving

I have 2 tables

banks table

create table `banks` (
`bank_id` int ,
`bank_name` varchar (150),
`balance` double ,
`b_date` date ,
`delete_state` double
);
insert into `banks` (`bank_id`, `bank_name`, `balance`, `b_date`, `delete_state`) values('1','Emirates NBD','632008','2016-10-10','0');
insert into `banks` (`bank_id`, `bank_name`, `balance`, `b_date`, `delete_state`) values('3','HABIB BANK LIMITED','1134484','2016-10-10','0');


cheque table

create table `cheque` (
`ch_id` int ,
`bank_id` int ,
`amount` double ,
`status` int,
`delete_state` double
);
insert into `cheque` (`ch_id`, `bank_id`, `amount`, `status`, `delete_state`) values('4','1','15000','2','0');
insert into `cheque` (`ch_id`, `bank_id`, `amount`, `status`, `delete_state`) values('9','1','250000','1','0');


My MYSQL Query is

SELECT bk.*, SUM(amount) AS tot_amount, (bk.balance - SUM(amount)) AS bank_balance FROM banks bk LEFT JOIN cheque ch ON bk.bank_id = ch.bank_id
WHERE ch.status = 1 AND bk.delete_state=0 AND ch.delete_state = 0


I need to join these 2 tables and get from bank table all the bank_name's even though cheque table doesn't have any entry..

But current my query is giving when cheque table having entry only, So its returning only one bank result.. please check and let me know where I'm missing!!

Answer

You need to group by bank_id. When you group a question you get the result for each value for the variable you group on.

SELECT bk.*, SUM(amount) AS tot_amount, (bk.balance - SUM(amount)) AS bank_balance 
FROM banks bk 
LEFT JOIN cheque ch ON (bk.bank_id = ch.bank_id  AND ch.status = 1 AND ch.delete_state = 0)
WHERE bk.delete_state=0 
GROUP BY bk.bank_id;

SQL Fiddle