1. Discussion Forums
    2. dotnetspider
    3. [Competition Entries]
You must Sign In to post a response.
  • Category: [Competition Entries]
    Points:5

    How to display data using 2 tables in sql


    Are you looking for way to get a sql query to display data using 2 tables in sql ? then read this thread to know more about it



    Hi all,
    I have the 2 tables EmpLoans and EmpPayments, tables Data like below

    EmpLoans
    UserId LoanAmt
    229 15000
    229 3000
    229 12500

    EmpPayments

    UserId Date LoanInstallment RemainingAmt
    229 1/31/2013 1500 29000
    229 2/28/2013 2500 26500
    229 3/31/2013 3500 23000
    229 4/30/2013 3000 20000

    I want the data like below using sql query

    UserId Date Sum(LoanAmt) LoanInstallment RemainingAmt
    229 1/31/2013 30500 1500 29000
    229 2/28/2013 30500 2500 26500
    229 3/31/2013 30500 3500 23000
    229 4/30/2013 30500 3000 20000

    Can anybody help me the query display like above.
    Thanks,
  • #719509

    Points:1
    You need to join two table on common column UserId and select your desired result set and use UDF for calculate loan sum as follows

    Select EL.UserId , EP.Date, dbo.getSumLoan(EL.UserId),Ep.LoanInstallment, EP.RemainingAmt
    From EmpLoans EL ON EmpPayments EP on EL.UserId = EP.UserId

    And your UDF function for calcuate sum or loan as follows

    Create function dbo.getSumLoan(@UserId int)
    Return int
    As
    Begin
    	Declare @sum int
    	Select @sum =Sum(LoanAmt) From EmpLoans where Userid = @UserId
    	Return @sum
    End



    Hope this helps you
    Regards & thanks
    Arvind kumar
    Visit--blog.akumars.esoftera.in

  • #719513

    Points:2
    Hi,
    Try this

    Select EP.UserId,EP.Date,sum(EL.LoanAmt) as LoanAmount,EP.RemainingAmt from EmpLoans EL inner join EmpPayments EP on EP.UserId=EL.UserId where <condition> group by UserId
    "Enjoy the World of IT"

    Ranipriya

  • #719516

    Points:1
    Hi,

    You may try a simple JOIN for this Condition and also provide the aggregate function and group by clause in your query.

    Try the below query:

    Select a.UserId, b.Date, Sum(a.LoanAmt),b.LoanInstallment, b.RemainingAmt
    FROM  EmpLoans as a, EmpPayments as b
    WHERE a.UserId=b.UserId
    Group By UserId

  • #719519

    Points:3
    To Create table:-

    Create table EmpLoans (UserId int,LoanAmt int)


    Insert some values to EmpLoans:


    Insert into EmpLoans values (229,15000)
    Insert into EmpLoans values (229,3000)
    Insert into EmpLoans values (229,12500)

    Select * from EmpLoans
    EmpLoans

    UserId LoanAmt
    229 15000
    229 3000
    229 12500


    Create table EmpPayments (UserId int,date datetime,LoanInstallment int,RemainingAmt int)


    Insert into EmpPayments values (229,'1/31/2013',1500,29000)
    Insert into EmpPayments values(229,'2/28/2013',2500,26500)
    Insert into EmpPayments values(229,'3/31/2013',3500,23000)
    Insert into EmpPayments values(229,'4/30/2013',3000,20000)

    EmpPayments

    UserId Date LoanInstallment RemainingAmt
    229 1/31/2013 1500 29000
    229 2/28/2013 2500 26500
    229 3/31/2013 3500 23000
    229 4/30/2013 3000 20000

    I want the data like below using sql query


    Select h.UserId ,CONVERT(varchar(max),d.date,106) as Date ,SUM(h.LoanAmt) as LoanAmt,d.LoanInstallment ,d.RemainingAmt from EmpLoans H inner join EmpPayments D on h.UserId =d.UserId

    group by h.UserId ,d.LoanInstallment ,d.RemainingAmt,d.date


    Thanks & Regards
    G.Renganathan
    Nothing is mine ,Everything is yours!!!

  • #719534

    Points:1
    You can use inbuilt function of SQL for summing the column value. check following query for more detail

    Select a.UserId,a.Date,sum(a.LoanAmt) as LoanAmount,b.RemainingAmt 
    from  EmpLoans a and EmpPayments b 
    where a.UserId=b.UserId
    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]


    • This thread is locked for new responses. Please post your comments and questions as a separate thread.
    If required, refer to the URL of this page in your new post.
    Submit New Thread
    Return to Return to Discussion Forum

    Top Contributors

    Today
      Last 7 Days

      more...

      About dotnetspider.com

      We believe in providing quality content to our readers. If you have any questions or concerns regarding any content published here, feel free to contact us using the Contact link below.

      Quick Links
      General
      Services

      Digital Marketing by SpiderWorks Technologies, Kochi - India. ©