outer query to list only if its rowcount equates to inner subquery

Need help on a query using sql server 2005

I am having two tables

code

chargecode
chargeid
orgid

entry

chargeid
itemNo
rate

I need to list all the chargeids in entry table if it contains multiple entries having different chargeids which got listed in code table having the same charge code.

data :

code

100,1,100
100,2,100
100,3,100
101,11,100
101,12,100

entry

1,x1,1
1,x2,2
2,x3,2
11,x4,1
11,x5,1

using the above data , it query should list chargeids 1 and 2 and not 11.

I got the way to know how many rows in entry satisfies the criteria, but m failing to get the chargeids

select count (distinct chargeId)
from entry where chargeid in (select chargeid from code where chargecode = (SELECT A.chargecode
from code as A join code as B
ON A.chargecode = B.chargeCode and A.chargetype = B.chargetype and A.orgId = B.orgId AND A.CHARGEID = b.CHARGEid
group by A.chargecode,A.orgid
having count(A.chargecode) > 1)
)

From stackoverflow

• First off: I apologise for my completely inaccurate original answer.

  The solution to your problem is a self-join. Self-joins are used when you want to select more than one row from the same table. In our case we want to select two charge IDs that have the same charge code:

  SELECT DISTINCT c1.chargeid, c2.chargeid FROM code c1
  JOIN code c2 ON c1.chargeid != c2.chargeid AND c1.chargecode = c2.chargecode
  JOIN entry e1 ON e1.chargeid = c1.chargeid
  JOIN entry e2 ON e2.chargeid = c2.chargeid
  WHERE c1.chargeid < c2.chargeid
  

  Explanation of this:

  First we pick any two charge IDs from 'code'. The DISTINCT avoids duplicates. We make sure they're two different IDs and that they map to the same chargecode.

  Then we join on 'entry' (twice) to make sure they both appear in the entry table.

  This approach gives (for your example) the pairs (1,2) and (2,1). So we also insist on an ordering; this cuts to result set down to just (1,2), as you described.

  Jonathan Leffler : That's impressive - I sort of see what it is doing. Did he really need pairs of codes? I was more under the impression that a list of single values would be sufficient. I'm not sure whether that's simpler, but it should be.

  rjh : For a list of single values, remove the final WHERE and select only 'distinct c1.chargeid' rather than two IDs. I assumed that Nrj was migrating to a new schema, so having a list of dupes would be handy.