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



Responses

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

Up
3
Down

Resolved
@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

Up
2
Down

Resolved
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

Up
1
Down

Resolved
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

Up
0
Down
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

Up
-3
Down
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

Login to post response