Query with duplicates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query with duplicates

Author

Topic

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2011-01-11 : 21:30:48

Hi folks -

I have a table called adjclaims that has a listing of claimid numbers that have been adjusted. I have another table called claimline that lists the same claimid number with the claim line number and detail. My problem is that the adjclaims.claimid is unique and only appears once but the claimline.claimid is not unique and could appear many times depending on how many claim lines there are on each claim - there could be alot.

My data looks like this:
adjclaims table
claimid field
12345
12346
12347

claimline table
claimid field claimline field
12345 1
12346 1
12346 2
12346 3
12347 1
12347 2
12348 1

Desired output:
12345 1
12346 1
12346 2
12346 3
12347 1
12347 2
12348 1

Can this be done? Adjusted claims, unfortunately, do not appear in the claimline table.

Thanks!

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2011-01-11 : 22:09:24

Unless I am missing something, dispite what you say, your desired output is

select
claimid, field
from
claimline
order by
claimid, field

so your requirement or test data is suspect, or I've missed what you are saying

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2011-01-11 : 22:27:10

I am probably just not being clear enough. The adjusted claims table only has claims that have been adjusted and does not identify the claim line. The claim line table has all claims ever processed and includes the claim line number. I want to pull out the adjusted claims, including line # from the claim line table based on the claims existence in the adjusted claims table. Does that help??

quote:
Originally posted by LoztInSpace

Unless I am missing something, dispite what you say, your desired output is

select
claimid, field
from
claimline
order by
claimid, field

so your requirement or test data is suspect, or I've missed what you are saying

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2011-01-11 : 22:28:05

Roughly 20% of all claims are adjusted.....

quote:
Originally posted by jcb267

I am probably just not being clear enough. The adjusted claims table only has claims that have been adjusted and does not identify the claim line. The claim line table has all claims ever processed and includes the claim line number. I want to pull out the adjusted claims, including line # from the claim line table based on the claims existence in the adjusted claims table. Does that help??
quote:
Originally posted by LoztInSpace

Unless I am missing something, dispite what you say, your desired output is

select
claimid, field
from
claimline
order by
claimid, field

so your requirement or test data is suspect, or I've missed what you are saying

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2011-01-11 : 23:51:29

select
cl.claimid, cl.field
from
claimline cl inner join ajdclaim a on(cl.claimid=a.claimid)
order by
cl.claimid, cl.field

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2011-01-13 : 11:44:25

Oh, thanks. I am trying it now.......

quote:
Originally posted by LoztInSpace

select
cl.claimid, cl.field
from
claimline cl inner join ajdclaim a on(cl.claimid=a.claimid)
order by
cl.claimid, cl.field

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources