Re: SQL Query with only Duplicates...

"Logan Yserver" <gollum211_at_nospam.hotmail.com> wrote in message news:slrnas34ts.983.gollum211_at_sith.adm.louisville.edu...
> In article <3DC1819F.1C69863D_at_exesolutions.com>, Daniel Morgan wrote:
> > Logan Yserver wrote:
> >>
> >> EID PCN DID
> >> ---- ---- --------
> >> 1000 0070 21212121
> >> 1001 0080 32323232
> >> 1001 0020 43434343
> >> 1020 0080 65656565
> >> 1020 0080 76767676
> >> 1020 0010 10101010
> >>
> >>
> >> EID PCN DID
> >> ---- ---- --------
> >> 1020 0080 65656565
> >> 1020 0080 76767676
> >>
>
> >
> > SELECT eid, pcn, did, COUNT(*)
> > FROM mytable
> > GROUP BY eid, pcn, did
> > HAVING COUNT(*) > 1;
> >
> > If you only want part of the information ... use it within an in-line
view as in:
> >
> > SELECT eid, pcn
> > FROM (
> > SELECT eid, pcn, did, COUNT(*)
> > FROM mytable
> > GROUP BY eid, pcn, did
> > HAVING COUNT(*) > 1);
> >
> Daniel,
>
> Thanks for the reply, and this is the path that I first went down, however
it
> only gives you duplicats for all three columns (eid, pcn, did) but what I
want
> is when 'eid', 'pcn' are the same, but 'did' is different. And I only
want to
> display the lines that are different. If the eid only shows up once,
there is
> no way they will end up in the output.
>
> I cannot go the otherway (count < 2) because I get everyone that has a one
> line entry. So I look at starting with a view of data for a employees who
> have two or more of the same 'pcn'. Now I simply have to remove the the
> entries wher all three columns repeat (as you have shown) and that should
be
> my answer. Or is it?
>

Try these. It implements exactly what you said.

select eid, pcn, did
from mytable where (eid, pcn) in (

    select a.eid, a.pcn
    from mytable a, mytable b

    where a.eid = b.eid and
          a.pcn= b.pcn and
          a.did <> b.did );

Here is the output
     1020 0080                           65656565
     1020 0080                           76767676

-
Jon Yi Received on Thu Oct 31 2002 - 15:36:27 CST