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)

So, the result should be :

Card_id   Nbr_Cards
1    ..   2
2    ..   0
3    ..   2
4    ..   0
5    ..   0
6    ..   2

Answer 1

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

Answer 2

If I understood your question.
For each card count the number of exactly equal details:

declare @CardDetails table (card_id int, leve_id int)

insert into @CardDetails values
(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)


select card_id,
       count(*) over(partition by leve_ids) - 1 as EqualCount
from 
  (
    select card_id,
           (select ','+cast(leve_id as varchar(10))
            from @CardDetails as C2
            where C1.card_id = C2.card_id
            order by C2.leve_id
            for xml path('')) as leve_ids
    from @CardDetails as C1
    group by card_id
  ) T
order by card_id

Result:

card_id     EqualCount
----------- -----------
1           2
2           0
3           2
4           0
5           0
6           2