T-Sql (Complex?) query

Go To StackoverFlow.com

3

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
2012-04-04 21:15
by Alex Jean
The best way to get a good response when asking SQL question is to provide example data, and the desired result data set, that illustrates your situation - this leaves less up to interpretation - Jon Egerton 2012-04-04 21:18
Shouldn't 6 have a Nbr_Cards as 2 - Mikael Eriksson 2012-04-04 21:45
Yes. Thank you for pointing it out - Alex Jean 2012-04-04 21:55
If I read correctly, this is an exact Relational Division problem - ypercubeᵀᴹ 2012-04-04 23:08
Yes it is! And it's a pretty good read also - Alex Jean 2012-04-05 14:16


4

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
2012-04-04 21:27
by Conrad Frix
I'm gonna try this on my tables. I'll let you know if it works soon. Thank you for the input - Alex Jean 2012-04-04 21:47
Works too. Thanks alot for your time - Alex Jean 2012-04-05 14:07


4

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
2012-04-04 21:34
by Mikael Eriksson
I'm gonna try this on my tables. I'll let you know if it works soon. Thank you for the input - Alex Jean 2012-04-04 21:47
This works like a charm! Thank yo - Alex Jean 2012-04-04 22:05
Ads