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

      

+3


source to share


2 answers


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

      

+4


source


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

      

+4


source







All Articles