T-Sql Query (Complex?)
I am trying to make a request, but I cannot 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's the problem: every card has a list of parts.
I want the query to be counted for each card, the number of cards that have the same data except for the card itself. This means the same list leve_id
.
Is it possible to achieve this in normal t-sql?
Hope I was clear enough, if not I will try to better explain what I need.
Edit:
I really don't need to know what cards he is at the moment, but he would surely get bonus points if that happened.
Edit # 2: So let's 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
source to share
If you understood 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
source to share
If I understood your question.
For each card, the number of exactly equal parts is calculated:
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
source to share