Re: SQL Query with only Duplicates...

Logan Yserver wrote:

> I wish I knew how to say what I am trying to accomplish with a query. The fact
> that I cannot, might explain why I have a hard time developing SQL to
> extract the data I want. So I thought I would throw an example out to you guys
> in hopes that you can help me solve the query problem.
>
> Example:
> One table called 'BUDGET'. In this table there are many things, but the key
> fields are employee number (EID), position number (PCN) and funding department
> id (DID). It is possible for person in one position to be funded from two
> different departments. And out of all of the data, I simply want to show the
> guys who have this condition. So given the sample table:
>
> EID PCN DID
> ---- ---- --------
> 1000 0070 21212121
> 1001 0080 32323232
> 1001 0020 43434343
> 1020 0080 65656565
> 1020 0080 76767676
> 1020 0010 10101010
>
> ...Out of all of this sample data, the only guy to show up in my query is
> EID=1020, PCN=0080. Because he is one guy in the same position being paid from
> two different departments. The other are either different PCNs or they simply
> have only one entry. The output would look like:
>
> EID PCN DID
> ---- ---- --------
> 1020 0080 65656565
> 1020 0080 76767676
>
> This could even use a break to look like:
>
> EID PCN DID
> ---- ---- --------
> 1020 0080 65656565
> 76767676
>
> Again, all other will be ignored. I think I have provided enough of an
> example to cover the query. The actual table has quiet a few fields but this
> should be all I require to dig through.
>
> Any idea how to create and SQL or even PL/SQL code to just grab the data I am
> searching for a report? I have tried counts and in-line views, but I just
> cannot seem to work out the logic.
>
> Thanks all...
>
> --
>
> gollum211_at_nospam.hotmail.com
> [Note: Remove "nospam." hostname.]

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 Morgan Received on Thu Oct 31 2002 - 13:16:52 CST