April 25, 2002 at 6:43 am
I need to run a query that joins 3 tables and returns fields from each table. Each claim has up to 4 rows with the same ID number. I need my results to include only claims that have more than 1 row with the same date and service code. However, if the claim does have more than the 1 row I need all rows to be in the results set.
I am really at a loss. Please help.
KB
KB
April 25, 2002 at 7:00 am
Can you post DDL for the tables? Makes it a lot easier for us to work on!
Andy
April 25, 2002 at 7:18 am
Table1 and Table2 join on claimID, recdDate, and recdtime. Table2 and Table3 join on claimID, recdDate, recdTime, and DetailLn. The criteria for the query is T1.status = 'C' and any claims that have 2 lines with T3.remitcode = 'P74' and T1.DateofService is the same.
Does this help? I found in an earlier post:
select b.firstname, b.lastname, b.middlename, a.dob, count(*)
from emp a
inner join emp b
on a.emp_id = b.emp_id
group by b.firstname, b.lastname, b.middlename, a.dob
having count(*) > 1
This looks like what I need to do, but I don't think it will give me all claim lines for the claims that have the matching service code and date of service.
Thanks!
KB
KB
April 25, 2002 at 8:09 am
When we refer to the DDL of a table we are referring to the CREATE statement to make the tables ourselves to test. This can be quickly obtained by using EM and right clicking on the table(s) individually in question and doing copy, then paste into notepad then all directly to the site.
Now for the example there are some ins and outs that can make this easier on you.
This should work for you
SELECT *
FROM
TABLE1 T1
INNER JOIN
TABLE2 T2
INNER JOIN
TABLE3 T3
ON
T2.claimID = T3.claimID AND
T2.recdDate = T3.recdDate AND
T2.recdTime = T3.recdTime AND
T2.DetailLn = T3.DetailLn
ON
T2.claimID = T1.claimID AND
T2.recdDate = T1.recdDate AND
T2.recdTime = T1.recdTime
WHERE
T1.cliamID IN (SELECT claimID FROM Table1 GROUP BY claimID HAVING COUNT(claimID) > 1)
The last part for the claimid keeps you from having to list every field in a the group by clause for every table. Also you should not use * for your select but the fields you want to output and reference using the table alias from such as ClaimID from table1 would be T1.ClaimID. This way you have only one column with that name output.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 26, 2002 at 12:37 am
here's extending the query a bit
SELECT *
FROMTABLE1
INNER JOINTABLE2
ON TABLE2.claimID = TABLE1.claimID AND
TABLE2.recdDate = TABLE1.recdDate AND
TABLE2.recdTime = TABLE1.recdTime
INNER JOINTABLE3
ONTABLE2.claimID = TABLE3.claimID AND
TABLE2.recdDate = TABLE3.recdDate AND
TABLE2.recdTime = TABLE3.recdTime AND
TABLE2.DetailLn = TABLE3.DetailLn
INNER JOIN
(
SELECT TABLE1.ClaimID
FROMTABLE1
INNER JOINTABLE2
ON TABLE2.claimID = TABLE1.claimID AND
TABLE2.recdDate = TABLE1.recdDate AND
TABLE2.recdTime = TABLE1.recdTime
INNER JOINTABLE3
ONTABLE2.claimID = TABLE3.claimID AND
TABLE2.recdDate = TABLE3.recdDate AND
TABLE2.recdTime = TABLE3.recdTime AND
TABLE2.DetailLn = TABLE3.DetailLn
WHERE (TABLE1.Status = 'C') AND
(TABLE3.remitcode = 'P74')
GROUP BY TABLE1.ClaimId , TABLE1.DateOfService
HAVING COUNT(*) > 1
) As TABLE4 ON TABLE4.ClaimId = TABLE1.ClaimId
however without some DDL this is just gonna be a guess
April 26, 2002 at 7:10 am
I love this website!
Thanks! I think that did it!
KB
KB
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply