[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 236
  • Last Modified:

multiple same records in join

Hi,
     I have a general question that seems hard to explain, I want to create a join btwn 3 tables; however, when I run my query I have multiple copy records appearing of the same record.  Even when I cut the query down to a two table join with one field this is happening.  I'm sure there is something fundamentally wrong with my logic that is making this happen.  I would like the query to only display each record once.  There aren't any PK's or relationship btwn the tables but they reside in the same DB.  Can anyone explain why this would happen?

select
a.cs,
b.tn, b.dist, b.al, b.si, b.bld, b.status, b.rcd, b.ct, b.cat, b.wfr,
b.ap, b.rap, b.dp1id, b.dp1tm, b.dp2id,
b.dp2tm, b.dist + b.bld as b_tnarea,
c.region
from
tbl_calrs_archive as a
join tbl_OpenCalrsData as b
on a.tn = b.tn and a.rcd = b.rcd
left outer join CALRSnnx as c
on b.dist + b.bld  = c.tnarea COLLATE Latin1_General_CI_AS
where
b.ct is null
and left(a.sa, 4) = '!MC!'
order by b.tn
0
marcus72
Asked:
marcus72
  • 2
2 Solutions
 
SQL_SERVER_DBACommented:
distinct maybe
0
 
SQL_SERVER_DBACommented:
max and group by....
0
 
marcus72Author Commented:
SQL SERVER DBA,
Distinct would work but the only thing I could make distinct would be the tn (telephone number) but there may be multiple records of the same tn so it wouldn't give me every record I am looking for.  What do you mean by MAX?
0
 
jogosCommented:
<Even when I cut the query down to a two table join with one field this is happening.>
The number of fields you select don't mather

        from   tbl_calrs_archive as a
        join tbl_OpenCalrsData as b on a.tn = b.tn and a.rcd = b.rcd

For the 'multiple' rows  do 2 selects (replace ????? with the corrsponding values from your multiple rows)
      select * from tbl_calrs_archive as a where a.tn = ???????  and a.rcd = ???????
      select * from tbl_OpenCalrsData as b where b.tn = ?????? and b.rcd = ???????
This will learn you that in one of the 2 tables there are more records witch will meat the join-criteria and will generate a separate row in the result. It doesn't show until you select one field that is diffrent.

If you don't kneed the multiples just put the distinct in front not just before one of the fields
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now