how to find which persion have all colorcodes using color table in sql server

Question

how to find which persion have all colorcodes using color table in sql server

asrinu13

Say Hey Kid

Points: 709
More actions
May 10, 2019 at 6:42 am

#3639663

Hi I have one doubt in sql server ,
how to find which persion have all colorcode using colors table.
table1: persioncolors
table2: personcolors
CREATE TABLE [dbo].[colors](
[colorcode] [varchar](50) NULL
)
CREATE TABLE [dbo].[PersionColors](
[Name] [varchar](50) NULL,
[ColorCode] [varchar](50) NULL
)
INSERT [dbo].[colors] ([colorcode]) VALUES (N'Red')
GO
INSERT [dbo].[colors] ([colorcode]) VALUES (N'Blue')
GO
INSERT [dbo].[colors] ([colorcode]) VALUES (N'Green')
GO
INSERT [dbo].[PersionColors] ([Name], [ColorCode]) VALUES (N'Tom', N'Red')
GO
INSERT [dbo].[PersionColors] ([Name], [ColorCode]) VALUES (N'Tom', N'Blue')
GO
INSERT [dbo].[PersionColors] ([Name], [ColorCode]) VALUES (N'Tom', N'Green')
GO
INSERT [dbo].[PersionColors] ([Name], [ColorCode]) VALUES (N'Tom', N'Brown')
GO
INSERT [dbo].[PersionColors] ([Name], [ColorCode]) VALUES (N'Joe', N'Red')
GO
INSERT [dbo].[PersionColors] ([Name], [ColorCode]) VALUES (N'Joe', N'Blue')
GO
INSERT [dbo].[PersionColors] ([Name], [ColorCode]) VALUES (N'Joe', N'Green')
GO
INSERT [dbo].[PersionColors] ([Name], [ColorCode]) VALUES (N'Hari', N'Red')
GO
INSERT [dbo].[PersionColors] ([Name], [ColorCode]) VALUES (N'Hari', N'Blue')
GO
INSERT [dbo].[PersionColors] ([Name], [ColorCode]) VALUES (N'Nani', N'Blue')
GO
INSERT [dbo].[PersionColors] ([Name], [ColorCode]) VALUES (N'Test', N'Orange')
GO
INSERT [dbo].[PersionColors] ([Name], [ColorCode]) VALUES (N'Hari', N'ye')
GO
INSERT [dbo].[PersionColors] ([Name], [ColorCode]) VALUES (N'a', N'j')
GO
INSERT [dbo].[PersionColors] ([Name], [ColorCode]) VALUES (N'a', N'c')
GO
INSERT [dbo].[PersionColors] ([Name], [ColorCode]) VALUES (N'a', N't')
GO
INSERT [dbo].[PersionColors] ([Name], [ColorCode]) VALUES (N'a', N'u')
GO
INSERT [dbo].[PersionColors] ([Name], [ColorCode]) VALUES (N'b', N'u')
GO
INSERT [dbo].[PersionColors] ([Name], [ColorCode]) VALUES (N'b', N'i')
GO
INSERT [dbo].[PersionColors] ([Name], [ColorCode]) VALUES (N'b', N'o')
Base on above two tables I want output like below :
Name
Joe
Tom
I tried like below
select name from [dbo].[PersionColors] p
join colors c
on p.colorcode=c.colorcode
group by name
having (count(distinct p.colorcode ))=(select count(*)cnt from colors)
above query is giving expeted result. but query is taking more time while using distinct clasue
in above two tables have large data set(billons of records)
can you please tell me any alternative solution to achive this task in sql server

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

What indexes do you have on the tables? Please post an actual execution plan for the query (the .sqlplan file, not just a picture, please).

John

Jonathan AC Roberts SSCoach Points: 18352 More actions · Answer 2

Here is an alternative way to get the results. I think it should perform quite well if you have appropriate indexes on the tables.

Do you have a "Persons" table? If so the SQL could be made more efficient.

This will also find the correct answer even if there are duplicates on the PersonColors table:

-- People with all colours present in colors table
SELECT DISTINCT Name
  FROM [dbo].[PersionColors] pc
 WHERE NOT EXISTS(SELECT *
                    FROM [dbo].[colors] c
                   WHERE NOT EXISTS(SELECT * 
                                      FROM [dbo].[PersionColors] pc2
                                     WHERE pc2.Name = pc.Name
                                       AND pc2.ColorCode = c.colorcode))
--
-- People without all colours present in colors table
--
SELECT DISTINCT Name
  FROM [dbo].[PersionColors] pc
 WHERE EXISTS(SELECT *
                FROM [dbo].[colors] c
               WHERE NOT EXISTS(SELECT * 
                                  FROM [dbo].[PersionColors] pc2
                                 WHERE pc2.Name = pc.Name
                                   AND pc2.ColorCode = c.colorcode))

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 3

John Mitchell-245523 wrote:

What indexes do you have on the tables? Please post an actual execution plan for the query (the .sqlplan file, not just a picture, please). John

Great suggestion that worked very well on the old engine. If you know a way to do that on this bloody new forum engine, I'd sure like to know how. 🙁

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)