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

Question

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)
)

Answer 1

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.