colors 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](50) 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

Could you please try the following:

SELECT P.PRODUCT_ID,C.Productcolor
FROM
 (
    SELECT DISTINCT PRODUCTID AS PRODUCT_ID FROM DBO.productInfo
 )P
CROSS JOIN
(
  SELECT DISTINCT PRODUCTCOLOR 
    FROM DBO.productInfo
)C
 EXCEPT
 SELECT X.PRODUCTID,X.PRODUCTCOLOR
  FROM DBO.productInfo AS X