Count rows that have the same relationships

Question

I have three tables. Palette, color and a relationship table palette_color. Just like this sample:

http://sqlfiddle.com/#!6/fe832/2

I would like to count the palettes that has the same exact colors in the relationship table. I am already doing it as you can see in the example. But I believe my method is not efficient. It takes almost 2 seconds to run.

I am using SQL Server.

This is where I count the rows:

(
SELECT count(DISTINCT palette_id) as total FROM palette_color COLOR
    WHERE NOT EXISTS
        (( (SELECT color_id FROM palette_color WHERE palette_id = PALETTE.id) EXCEPT (SELECT color_id FROM palette_color WHERE palette_id = COLOR.palette_id) )
        UNION ALL
        ( (SELECT color_id FROM palette_color WHERE palette_id = COLOR.palette_id) EXCEPT (SELECT color_id FROM palette_color WHERE palette_id = PALETTE.id) ))
 ) as total

And in the where clause, I make sure only the first palette appear on the result

WHERE  id =
(
    SELECT MIN(palette_id) FROM palette_color COLOR
        WHERE NOT EXISTS
            (( (SELECT color_id FROM palette_color WHERE palette_id = PALETTE.id) EXCEPT (SELECT color_id FROM palette_color WHERE palette_id = COLOR.palette_id) )
            UNION ALL
            ( (SELECT color_id FROM palette_color WHERE palette_id = COLOR.palette_id) EXCEPT (SELECT color_id FROM palette_color WHERE palette_id = PALETTE.id) ))
)

Answer 1

Here I create a string list of all the color_id in a palete_id using FOR XML PATH

Then group by and count each group of colors.

SQL FIDDLE DEMO (12ms)

with cList as (
    SELECT  p.id palette_id, 
            STUFF(( SELECT ',' + CAST(pc.color_id as varchar(10) )
                    FROM palette_color pc
                    WHERE pc.palette_id = p.id
                    ORDER BY pc.color_id
                  FOR
                    XML PATH('')
                  ), 1, 1, '') AS ColorList
    FROM palette p
)
select min(palette_id) palette_id, ColorList, count(*) Total
from cList
group by ColorList

Answer 2

You can do this with the FOR XML PATH:

Query 1:

SELECT MIN(palette_id), count(*), colors
FROM (
SELECT id as palette_id,
colors = STUFF((
          SELECT ',' + convert(nvarchar(20), pc.color_id)
          FROM palette_color pc
          WHERE pc.palette_id = palette.id
          ORDER BY pc.color_id
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM palette) a
GROUP BY colors

Results:

|    |    |    colors |
|----|----|-----------|
|  1 | 15 |       1,2 |
|  6 | 60 |     1,2,3 |
| 26 |  6 |   1,2,3,4 |
| 46 | 42 | 1,2,3,4,5 |
| 28 | 18 |     1,3,4 |
| 34 | 36 |   1,3,4,5 |

As a bonus, this solution gives you the actual colors used in the palette