October 4, 2007 at 9:14 am
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
October 4, 2007 at 9:33 am
Well - it's tough to know - since you've got records from 3 tables. One of the table's values will likely look like they're duplicating - but that's the nature of a join.
After all - if you had just 1 record in A, 3 in B, and each record in b match to 2 in C, the values from A would "duplicate" 6 times, each value of B would dupe 2 times. That's normal. If that kind of math isn't working, then there are a few places to look.
Start by looking to see if your joins are correct and are sufficient. You might even just start with just 2 tables and make sure they don't "duplicate" other than what you'd expect.
Otherwise - that means that your matchups are correct, but the REASON why you see multiple instances of the same compound record isn't included in the view. You could then either look at including the field in whichever table is the source of the duplicates, so that you can then see that there IS a difference between the records, or you can then use the DISTINCT clause, which will suppress the duplicates. Please note that by adding the Distinct clause - you will make the dataset read-only (fine for reports, but no longer updatable.)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply