CODEWITHSUNDEEP
mysql
Wazan
Wazan

Reputation: 539

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!!

Upvotes: 1

Views: 51

Answers (2)

Linkan
Linkan

Reputation: 579

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

Upvotes: 2

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

Try this:

SELECT bk.*,  
       SUM(CASE WHEN bk.delete_state=0 THEN amount ELSE 0 END) AS tot_amount, 
       SUM(CASE WHEN bk.delete_state=0 THEN bk.balance ELSE 0 END) 
        - 
       SUM(CASE WHEN bk.delete_state=0 THEN amount ELSE 0 END) 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
GROUP BY bk.`bank_id`, bk.`bank_name`

A few notes:

  • The query uses a GROUP BY clause, so a to return a separate record per bank_id.
  • The predicates pertinent to cheque table have been moved from WHERE to ON clause. Otherwise LEFT JOIN becomes an INNER JOIN.
  • The query uses conditional aggregation so as to only take into consideration records related to banks with bk.delete_state=0.

Upvotes: 0

Related Questions