help selecting duplicates - added in full query

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

help selecting duplicates - added in full query

Author

Topic

crugerenator
Posting Yak Master

126 Posts

Posted - 2007-10-15 : 16:59:17

[SELECT coalesce(a4.key,a3.key,a2.key,a.key) as parent_key,
a.key as child_key,
a.comp_name
FROM .......lots of JOINS..........]

that's what I have, and I need to find all instances where parent_key=child_key.

I've been trying something similiar to...

[SELECT name, count(name), name2, count(name2)
FROM people
GROUP BY name, name2
HAVING (count(name)>1 AND count(name2)>1)]

The problem is that I keep getting error messages because I can't use
the alias' or sum(coalesce(.....)).

I was hoping you guys might have a suggestion for me. Thanks ahead of time.

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2007-10-15 : 17:03:27

Hi:
This is what I did to select the duplicates for my report. It works, I hope this can give you an idea. I also need help. So, maybe we can work together now.

Select * From
(SELECT group_code,station_nbr, beg_eff_date, end_eff_date
From TIES_Temp.dbo.station_group
where (DATEADD(mm,DATEDIFF(mm,0,dateadd(mm,-0-datepart(day,0),getdate())),0) Between beg_eff_date and end_eff_date))
a,
(Select station_nbr, count(station_nbr) as ErrorDuplicate
From TIES_Temp.dbo.station_group
GROUP BY station_nbr
HAVING (COUNT(station_nbr) > 1 )) b
Where a.station_nbr = b.station_nbr

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-16 : 02:08:49

Maybe if you show us your complete query, we can help...

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 02:20:20

" Maybe if you show us your complete query, we can help..."

I think that is here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=91025

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-16 : 02:22:29

Not Orisa. That person has been helped plenty!
I was referring to crugerenator.

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 02:26:10

Ah, beg your pardon. I did wonder if you were becoming a sainted-saint!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-16 : 02:27:39

Maybe. I posted a suggestion to Osirisa at the link you provided above.

E 12°55'05.25"
N 56°04'39.16"

crugerenator
Posting Yak Master

126 Posts

Posted - 2007-10-16 : 11:50:20

[select coalesce(a5.cst_key,a4.cst_key,a3.cst_key,a2.cst_key,a.cst_key) as parent_key,
a.cst_key as child_key
from
co_org (NOLOCK)
left outer join co_cust a (NOLOCK)
on a.cst_key = org_cst_key
left outer join co_cust a2 (NOLOCK)
on a.cst_par_cst_key = a2.cst_key
left outer join co_org_ext ac2 (NOLOCK)
on a2.cst_key = ac2.org_cst_key_ext
left outer join co_cust p3 (NOLOCK)
on a2.cst_par_cst_key = a3.cst_key
left outer join co_org_ext ac3 (NOLOCK)
on a3.cst_key = ac3.org_cst_key_ext
left outer join co_cust a4 (NOLOCK)
on a3.cst_par_cst_key = a4.cst_key
left outer join co_org_ext ac4 (NOLOCK)
on a4.cst_key = ac4.org_cst_key_ext ]

Here's my complete query. Sorry I didn't post it before, I had to make some changes in order to post it. What I need to do is find where parent_key=child_key. There's an error with the data I'm working with, and in each case that parent_key=child_key the data loops. Once I find the keys that are the same, I can fix the bad relationships. Thanks again.

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 23:29:28

So are you good to go, or is this query ving you error / not giving the answer you need?

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-18 : 02:18:21

Table alias a5 is not even created in your query, so you will get an error running the query above.

E 12°55'05.25"
N 56°04'39.16"

crugerenator
Posting Yak Master

126 Posts

Posted - 2007-10-18 : 10:42:32