SQL query referencing 2 tables in SQL Server and ASP

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]
ORDER BY Start_Date ASC

the parameter values are as follows

MMColParam = Request.QueryString("claim_type")  
MMColParam2 = Request.QueryString("member_id")
MMColParam3 = session("dtstartLastYear")        
MMColParam4 = session("dtEndLastYear")    
ckawebcreationAsked:
Who is Participating?
 
BadotzConnect With a Mentor Commented:
Change

FROM dbo.claim_line

to

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.

0
 
ckawebcreationAuthor Commented:
will i need to reference the main table from the select list for each field e.g. claim_line.[Amounttobepaid]
0
 
BadotzCommented:
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_]

to:

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"
0
 
ckawebcreationAuthor Commented:
i think it works! i just need to test a bit futher, thanks for your help!!
0
 
BadotzCommented:
No worries - glad to help.
0
All Courses

From novice to tech pro — start learning today.