Getting the list of all customers not having sign documents

Transact-SQL
1

I have got 3 tables: customer, document and documentcustomer. What I want to get is a list of customers not having a document and which one.

Using the data shown below I would like to have a list showing
Paola "Doc Two" (as Paolo only has got "Doc One")
Willy "Doc One" (as Willy doesn't have any)
Willy "Doc Two"

CREATE TABLE "tblCustomer" (
"C_Id" INT NOT NULL,
"C_Name" NVARCHAR(50) NULL DEFAULT NULL ,
PRIMARY KEY ("C_Id"))

CREATE TABLE "tblDocument" (
"D_Id" INT NOT NULL,
"D_Name" NVARCHAR(50) NULL DEFAULT NULL ,
PRIMARY KEY ("D_Id"));

CREATE TABLE "tblDocumentCustomer" (
"Dc_Id" INT NOT NULL,
"Dc_C_Id" INT NOT NULL,
"Dc_F_Id" INT NOT NULL,
PRIMARY KEY ("Dc_Id"));

With the following data:
INSERT [tblCustomer] ([C_Id], [C_Name]) VALUES (1, N'John')
GO
INSERT [tblCustomer] ([C_Id], [C_Name]) VALUES (2, N'Paola')
GO
INSERT [tblCustomer] ([C_Id], [C_Name]) VALUES (3, N'Willy')
GO
INSERT [tblDocument] ([D_Id], [D_Name]) VALUES (1, N'Doc One')
GO
INSERT [tblDocument] ([D_Id], [D_Name]) VALUES (2, N'Doc Two')
GO
INSERT [tblDocumentCustomer] ([Dc_Id], [Dc_C_Id], [Dc_F_Id]) VALUES (1, 1, 1)
GO
INSERT [tblDocumentCustomer] ([Dc_Id], [Dc_C_Id], [Dc_F_Id]) VALUES (2, 1, 2)
GO
INSERT [tblDocumentCustomer] ([Dc_Id], [Dc_C_Id], [Dc_F_Id]) VALUES (3, 2, 1)
GO

2 
SELECT
    C.C_Name, D.D_Name
FROM tblCustomer C
CROSS JOIN tblDocument D
LEFT OUTER JOIN tblDocumentCustomer DC ON DC.Dc_C_Id = C.C_Id AND 
    DC.Dc_F_Id = D.D_Id
WHERE DC.Dc_C_Id IS NULL
ORDER BY C.C_Name, D.D_Name
1 Like
3

Thank you Scott. I had been thinking about the CROSS JOIN solution but had some doubt about its performance.

4

I understand what you mean. CROSS JOIN is bad when you don't really need it, and you often see that happen. But, when you do need it, as in this case, it's actually the most efficient way to do it.