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

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Kumaraspcode2009@gmail.com

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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 Kumaraspcode2009@gmail.com Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

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

Kumaraspcode2009@gmail.com

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

0	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

0	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 Kumaraspcode2009@gmail.com Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts