T-Sql (Complex?) query

Question

I'm trying to make a query, but I can't find a way to do it. So I got 3 tables ``` Table Card (card_id) Table Level(leve_id, leve_desc) Table CardDetails(cade_id, card_id, leve_id) ``` So here is the problem : Each cards got a list of details. I want a query to count for each cards, the number of cards who have the exact same details, excluding the card it-self. Which means the same list of `leve_id`. Is it possible to achieve it in plain t-sql? I hope I have been clear enough, if not, I'll try to explain better what I need. Edit: I don't really need to know which cards it is for the moment, but it would sure credit bonus points if it did. Edit #2: So lets say table Card (card_id) 1,2,3,4,5,6 ``` Table level (leve_id, leve_desc) (1, Level 1), (2,Level 2), (3,Level 3), (4,Level 4), (5, Level5), (6, Level6) Table CardDetails (card_id, leve_id) (1, 1), (1, 3), (1, 4), (2, 1), (2, 2), (3, 1) (3, 3), (3, 4), (4, 5), (5, 1), (5, 2), (5, 3) (5, 4), (5, 5), (5, 6), (6, 1), (6, 3), (6, 4) ```…

Accepted Answer

If I understand you correctly you want something like this

```
SELECT * 
FROM   cards c 
       INNER JOIN carddetails cd 
         ON c.card_id = cd.card_id 
       INNER JOIN (SELECT cade_id, 
                          leve_id 
                   FROM   carddetails 
                   GROUP  BY cade_id, 
                             leve_id 
                   HAVING COUNT (card_id) > 1)dups 
         ON cd.cade_id = dups.cade_id 
            AND cd.leve_id = dups.leve_id 
```

Or if you like COUNT OVER

```
with dups as  (
SELECT 
   COUNT(CARD_ID)  OVER (PARTITION BY cade_id, leve_id) cardCount
   cade_id,
   leve_id
FROM  carddetails 
)
    SELECT * 
    FROM   cards c 
           INNER JOIN carddetails cd 
             ON c.card_id = cd.card_id 
           INNER JOIN  dups 
             ON cd.cade_id = dups.cade_id 
                AND cd.leve_id = dups.leve_id 
    WHERE cardCount > 1
```

T-Sql (Complex?) query

Solution

Problem