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