How to solve this Query Bandi and Rajnilari2015 ( Most Urgent) [Resolved]

Posted by Jayakumars under Sql Server on 8/2/2016 | Points: 10 | Views : 445 | Status : [Member] [MVP] | Replies : 4
hi

i write MySql Query like this

select s.shareno,s.date as Dat,s.nameE,t.InstNameE,
Recptamt,Payamt , t.oldbalance as bal,
t.oldbalance + ifnull(sum(recptamt)-sum(Payamt),0) as opnbal ,
t.oldbalance + ifnull(sum(recptamt)-sum(Payamt),0)+Recptamt-Payamt
+ COALESCE((SELECT ifnull(SUM(Recptamt-Payamt),0)))as Total,
t.oldbalance + ifnull(sum(recptamt)-sum(Payamt),0) opnbal1
from `dbbank`.`tblsharemaster` as t
left join `dbbank`.`sharledg` as s on s.shareno=t.shareno;



But sharemaster have 3 or more records also sharledg 5 records
My need left join usage is match or not all records shows ?

but in this query output coming one record only this is wrong.

I need all records need to come becos i use left join so.

how to solve this

Mark as Answer if its helpful to you


Responses

Posted by: Rajnilari2015 on: 8/3/2016 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
Try this with Right Join

select s.shareno,s.date as Dat,s.nameE,t.InstNameE,

Recptamt,Payamt , t.oldbalance as bal,
t.oldbalance + ifnull(sum(recptamt)-sum(Payamt),0) as opnbal ,
t.oldbalance + ifnull(sum(recptamt)-sum(Payamt),0)+Recptamt-Payamt
+ COALESCE((SELECT ifnull(SUM(Recptamt-Payamt),0)))as Total,
t.oldbalance + ifnull(sum(recptamt)-sum(Payamt),0) opnbal1
from `dbbank`.`tblsharemaster` as t
right join `dbbank`.`sharledg` as s on s.shareno=t.shareno;


--
Thanks & Regards,
RNA Team

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 8/3/2016 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi

Rajnilari2015

full join not working in MySql , full join only sql server i think

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'full' at line 8


Mark as Answer if its helpful to you

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajnilari2015 on: 8/3/2016 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 25

Up
0
Down
Updated the answer

--
Thanks & Regards,
RNA Team

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 8/3/2016 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi
Rajnilari2015

not working in this Query. This is working for Sql server only. But i need MySQl only.

Mark as Answer if its helpful to you

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response