multiple same records in join

Posted on 2007-10-04
Last Modified: 2010-03-19
     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?

a.cs,, b.dist,,, b.bld, b.status, b.rcd, b.ct,, b.wfr,
b.ap, b.rap, b.dp1id, b.dp1tm, b.dp2id,
b.dp2tm, b.dist + b.bld as b_tnarea,
tbl_calrs_archive as a
join tbl_OpenCalrsData as b
on = and a.rcd = b.rcd
left outer join CALRSnnx as c
on b.dist + b.bld  = c.tnarea COLLATE Latin1_General_CI_AS
b.ct is null
and left(, 4) = '!MC!'
order by
Question by:marcus72
    LVL 16

    Expert Comment

    distinct maybe
    LVL 16

    Accepted Solution

    max and group by....

    Author Comment

    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?
    LVL 25

    Assisted Solution

    <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 = 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 = ???????  and a.rcd = ???????
          select * from tbl_OpenCalrsData as b where = ?????? 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

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    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…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    728 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

    18 Experts available now in Live!

    Get 1:1 Help Now