How to Fixed in this Join Query. [Resolved]

Posted by Jayakumars under Sql Server on 9/30/2015 | Points: 10 | Views : 1340 | Status : [Member] [MVP] | Replies : 5

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi

CREATE TABLE [dbo].[tblloanmaster](
[ShareNo] [int] NULL,
[LoanNo] [decimal](20, 0) NULL,
[Loanamt] [decimal](18, 2) NULL,
[Balanceamount] [decimal](18, 2) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[tblsharemaster](
[ShareNo] [int] NULL,
[NameE] [varchar](50) NULL,
[Balance] [decimal](10, 2) NULL
) ON [PRIMARY]

Select * from tblsharemaster
Select * from tblloanmaster

Insert into tblsharemaster VALUES(100,'AA1',1000)
Insert into tblsharemaster VALUES(101,'AA2',2000)
Insert into tblsharemaster VALUES(102,'AA3',3000)

Insert into tblloanmaster VALUES(100,1,0,0)
Insert into tblloanmaster VALUES(100,2,2000,45000)
Insert into tblloanmaster VALUES(102,3,3000,8500)
Insert into tblloanmaster VALUES(103,4,3000,8500)
Insert into tblloanmaster VALUES(102,5,0,0)
Insert into tblloanmaster VALUES(101,6,0,0)

Select * from tblsharemaster a left join tblloanmaster b on a.shareNo=b.ShareNo

-- so for output this
Shareno namee Balance ShareNo Loanno loanamt balanceamt
100 AA1 1000.00 100 1 0.00 0.00
100 AA1 1000.00 100 2 2000.00 45000.00
101 AA2 2000.00 0 101 0 0
102 AA3 3000.00 102 3 3000.00 8500.00
102 AA3 3000.00 102 5 0.00 0.00

-- But I need Output this

--ShareNo NameE LoanNo Loanamt
100 AA1 2 2000.00
101 AA2 6 0
102 AA3 3 3000.00

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Rajnilari2015 on: 9/30/2015 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 50

3	@JayaKumars, Since you tagged as Sql Server , so I have answered like that. However, here is an ANSI SQL program that should work in all RDBMS irrespective of version(s) SELECT X.ShareNo ,Z.NameE ,Y.LoanNo ,X.Loanamt FROM (SELECT a.ShareNo ,Loanamt=MAX(b.Loanamt) FROM tblsharemaster a LEFT JOIN tblloanmaster b ON a.shareNo=b.shareNo GROUP BY a.ShareNo) X INNER JOIN tblloanmaster Y ON X.ShareNo = Y.ShareNo AND X.Loanamt = Y.Loanamt INNER JOIN tblsharemaster Z ON X.ShareNo = Z.ShareNo ORDER BY X.ShareNo Result ShareNo NameE LoanNo Loanamt 100 AA1 2 2000.00 101 AA2 6 0.00 102 AA3 3 3000.00 Hope this helps -- Thanks & Regards, RNA Team Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

@JayaKumars,
Since you tagged as Sql Server , so I have answered like that. However, here is an ANSI SQL program that should work in all RDBMS irrespective of version(s)

SELECT 

	X.ShareNo

	,Z.NameE

	,Y.LoanNo

	,X.Loanamt

	

FROM

  (SELECT 

	a.ShareNo

	,Loanamt=MAX(b.Loanamt)

  FROM tblsharemaster a

  LEFT JOIN tblloanmaster b ON a.shareNo=b.shareNo

  GROUP BY a.ShareNo) X  

INNER JOIN tblloanmaster Y ON X.ShareNo = Y.ShareNo AND X.Loanamt = Y.Loanamt

INNER JOIN tblsharemaster Z ON X.ShareNo = Z.ShareNo 

ORDER BY X.ShareNo

Result

ShareNo	NameE	LoanNo	Loanamt

100	AA1	2	2000.00

101	AA2	6	0.00

102	AA3	3	3000.00

Hope this helps

--
Thanks & Regards,
RNA Team

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

Posted by: Rajnilari2015 on: 9/30/2015 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 50

2	You was very close (: However, you only need to pick up the records of those shares in their individual groups whose LoanAmount is highest. I modified your existing query a little bit to achieve this. ;WITH CTE AS( SELECT a.ShareNo ,a.NameE ,b.LoanNo ,b.Loanamt ,Rn = Row_Number() OVER(PARTITION BY a.ShareNo ORDER BY b.Loanamt DESC) FROM tblsharemaster a LEFT JOIN tblloanmaster b ON a.shareNo=b.ShareNo ) SELECT c.ShareNo ,c.NameE ,c.LoanNo ,c.Loanamt FROM CTE c WHERE c.Rn = 1 The trick was done by Row_Number() OVER(PARTITION BY a.ShareNo ORDER BY b.Loanamt DESC) Result ShareNo NameE LoanNo Loanamt 100 AA1 2 2000.00 101 AA2 6 0.00 102 AA3 3 3000.00 Hope this helps -- Thanks & Regards, RNA Team Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

You was very close (: However, you only need to pick up the records of those shares in their individual groups whose LoanAmount is highest. I modified your existing query a little bit to achieve this.

;WITH CTE AS(

SELECT 

	a.ShareNo

	,a.NameE

	,b.LoanNo

	,b.Loanamt

	,Rn = Row_Number() OVER(PARTITION BY a.ShareNo ORDER BY b.Loanamt DESC)

FROM tblsharemaster a 

LEFT JOIN tblloanmaster b ON a.shareNo=b.ShareNo )

SELECT 

	c.ShareNo

	,c.NameE

	,c.LoanNo

	,c.Loanamt

FROM CTE c WHERE c.Rn = 1

The trick was done by Row_Number() OVER(PARTITION BY a.ShareNo ORDER BY b.Loanamt DESC)
Result

ShareNo	NameE	LoanNo	Loanamt

100	AA1	2	2000.00

101	AA2	6	0.00

102	AA3	3	3000.00

Hope this helps

--
Thanks & Regards,
RNA Team

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

Posted by: Jayakumars on: 9/30/2015 [Member] [MVP] Bronze | Points: 25

1	Hi Good one Thanks reply. Little bit change i modified SELECT X.ShareNo,Z.NameE,Y.LoanNo,X.Loanamt FROM (SELECT a.ShareNo,MAX(b.Loanamt)as Loanamt FROM tblsharemaster a LEFT JOIN tblloanmaster b ON a.shareNo=b.shareNo GROUP BY a.ShareNo) X INNER JOIN tblloanmaster Y ON X.ShareNo = Y.ShareNo AND X.Loanamt = Y.Loanamt INNER JOIN tblsharemaster Z ON X.ShareNo = Z.ShareNo ORDER BY X.ShareNo Mark as Answer if its helpful to you Kumaraspcode2009@gmail.com Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

Hi

Good one Thanks reply.

Little bit change i modified

SELECT X.ShareNo,Z.NameE,Y.LoanNo,X.Loanamt FROM
(SELECT a.ShareNo,MAX(b.Loanamt)as Loanamt FROM tblsharemaster a
LEFT JOIN tblloanmaster b ON a.shareNo=b.shareNo
GROUP BY a.ShareNo) X
INNER JOIN tblloanmaster Y ON X.ShareNo = Y.ShareNo AND X.Loanamt = Y.Loanamt
INNER JOIN tblsharemaster Z ON X.ShareNo = Z.ShareNo
ORDER BY X.ShareNo

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: Jayakumars on: 9/30/2015 [Member] [MVP] Bronze | Points: 25

0	Hi Thanks Good Rajnilari2015 But how to use this Query in Mysql Query can you give mysql query for this thats helpful for me. 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: Serenamikell on: 9/30/2015 [Member] Starter | Points: 25

-3	http://www.w3schools.com/sql/ is the beat resourse for leaning Mysql. _______________________________________________________________________________ Helpwritinganessay.com Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts