SQL query referencing 2 tables in SQL Server and ASP

Posted on 2007-07-22
Last Modified: 2010-03-20
I have the followeing query which works fine, but there is another bit of data i need that is in another table.  The other bit of data is a field called [Payment Method Code] and it is in the claim_header table, it relates to this query in that the [Claim No_] are the same in both tables, this code just tells the user how they were paid.

I am not sure how to achieve this because the [Claim No_] does not get returned until the query is run, therefore how would I get the data from the other table??

I do not know if i could use the results from this query to then query the other table either.  

The results will be displayed as follows...

Claim No. | Treatment Date | Claimant Name | Payment Method |  Amount Claimed
Any ideas, I can explain further if needed?  Cheers

SELECT SUM(Amounttobepaid) AS Claim_amounts, [Amounttobepaid], [Claimant No_], [Claim No_], [Start_Date], [Benefit Code]
FROM dbo.claim_line
WHERE [Claimant No_] = MMColParam2 AND [Benefit Code] = MMColParam AND [Start_Date] >= MMColParam3  AND [Start_Date] <= MMColParam4 Group By  [Amounttobepaid], [Claimant No_],[Claim No_], [Start_Date], [Benefit Code]

the parameter values are as follows

MMColParam = Request.QueryString("claim_type")  
MMColParam2 = Request.QueryString("member_id")
MMColParam3 = session("dtstartLastYear")        
MMColParam4 = session("dtEndLastYear")    
Question by:ckawebcreation
    LVL 29

    Accepted Solution


    FROM dbo.claim_line


    FROM dbo.claim_line INNER JOIN claim_header ON dbo.claim.[Claim No_]=claim_header.[Claim No_]

    and add the fields from the JOINed table to your select list.


    Author Comment

    will i need to reference the main table from the select list for each field e.g. claim_line.[Amounttobepaid]
    LVL 29

    Expert Comment

    Yes. Further, you must prefix each column name with the correct table name. To reduce your typing, use an alias for each table. Change:

    FROM dbo.claim_line INNER JOIN claim_header ON dbo.claim.[Claim No_]=claim_header.[Claim No_]


    FROM dbo.claim_line AS cl INNER JOIN claim_header AS ch ON dbo.claim.[Claim No_]=claim_header.[Claim No_]

    Then you can use "cl" instead of "dbo.claim_line" and "ch" instead of "claim_header"

    Author Comment

    i think it works! i just need to test a bit futher, thanks for your help!!
    LVL 29

    Expert Comment

    No worries - glad to help.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now