color exist or not in sql server

Question

HI i have one doubt in sql server .
how to find expeted colors exist or not if not exits
then get productid informaton

table : productinfo
CREATE TABLE [dbo].[productInfo](
[Productid] [int] NULL,
[Productcolor] varchar NULL
)

INSERT [dbo].[productInfo] ([Productid], [Productcolor]) VALUES (1, N'red')
INSERT [dbo].[productInfo] ([Productid], [Productcolor]) VALUES (1, N'blue')
INSERT [dbo].[productInfo] ([Productid], [Productcolor]) VALUES (1, N'white')
INSERT [dbo].[productInfo] ([Productid], [Productcolor]) VALUES (2, N'red')
INSERT [dbo].[productInfo] ([Productid], [Productcolor]) VALUES (2, N'blue')
INSERT [dbo].[productInfo] ([Productid], [Productcolor]) VALUES (3, N'red')
INSERT [dbo].[productInfo] ([Productid], [Productcolor]) VALUES (4, N'red')
INSERT [dbo].[productInfo] ([Productid], [Productcolor]) VALUES (4, N'blue')
INSERT [dbo].[productInfo] ([Productid], [Productcolor]) VALUES (4, N'white')

based on above data I want output like below
Productid | Productcolor
2 |white missed
3 |blue missed
3 |white missed

each productid should have red,blue,white colors .if any colore not exist then
need to display that productid and color.if all colors exist then no need to display productid

I tried like below

select * from productinfo where Productcolor not in ( 'red','blue','white')

could you please tell me how to write a query to achive this task in sql server .

Answer 1

Check this query:

; 
with MC as
(
    select Productid, c as ProductColor
    from productInfo
    cross apply (values ('red'), ('blue'), ('white')) t(c)
    except
    select Productid, ProductColor
    from productInfo
)
select ProductId, ProductColor + ' missed' as ProductColor
from MC
order by ProductId, ProductColor

Answer 2

Hi @tani oju ,

Thank you so much for posting here in Microsoft Q&A.

Please also refer below:

;with cte as (  
SELECT distinct [Productid],b.color  
FROM    productinfo a  
CROSS JOIN (values ('red'), ('blue'), ('white')) b(color))  
select Productid,color+ ' missed' ProductColor   
from cte a where not exists   
(select 1 from productinfo b where b.Productid=a.Productid and a.color=b.Productcolor)  

Output:

Productid	ProductColor  
2	white missed  
3	blue missed  
3	white missed  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi @tani oju ,

Please also refer another method from below:

;WITH base (color) as (  
    SELECT 'red' UNION ALL  
    SELECT 'blue' UNION ALL  
    SELECT 'white'  
),  
Product as (  
    SELECT DISTINCT [Productid]  
    FROM productinfo t      
),  
pairs as (   
    SELECT *  
    FROM Product, base  
)      
SELECT a.Productid,a.color+ ' missed' ProductColor   
FROM pairs a  
LEFT JOIN productinfo b  
       ON b.Productid=a.Productid and a.color=b.Productcolor  
WHERE b.Productid IS NULL    

Output:

 Productid    ProductColor  
 2    white missed  
 3    blue missed  
 3    white missed  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

@tani oju
I would say safest method would be not to hardcode the colors as we cant be sure it will be always Red, Blue and White alone and not anything else
Better use the table itself as reference to get distinct color list and use it for getting missed one for each ids so that regardless of the colors that are actually present, the logic will always work fine

Like

SELECT i.Productid, c.Productcolor  
FROM (SELECT DISTINCT Productid FROM productinfo) i  
CROSS JOIN (SELECT DISTINCT Productcolor FROM productinfo) c  
WHERE NOT EXISTS  
(  
SELECT 1  
FROM productinfo  
WHERE Productid = i.Productid  
AND Productcolor = c.Productcolor  
)