It is not a general question. It is all about my 3 tables and I couldn't figure out where to start. Basically I have got 3 tables. ClientModels, DolsMcaItemModels and DolsMcaClientModels.
ClientModels - All the clients and their ClientID
DolsMcaItemModels - List of documents, there is a mandatory column.
DolsMcaClientModels - This table keeps all client DolsMcaItemModels.
I am trying to get list of clients who don't have mandatory documents in DolsMcaClientModels.
For example, if I have got a Mandatory "Passport" Document in DolsMcaItemModels and I want to get a list of clients who don't have a passport.
CLIENT TABLE
SELECT C.[ClientID]
,[ClientName]
,[ClientDOB]
FROM [dbo].[ClientModels]
DOCUMENT LIST
SELECT [DolsMcaItemID]
,[DolsMcaItemName]
,[DolsMcaItemMandatory]
,[DolsMcaItemStatus]
FROM [dbo].[DolsMcaItemModels]
WHERE [DolsMcaItemStatus] = true
Client Document Table
SELECT [DolsMcaClientID]
,[DolsMcaItemID]
,[ClientID]
,[DolsMcaClientItemStatus]
FROM [dbo].[DolsMcaClientModels]
So far what i did List of clients who don't have any documents
SELECT C.[ClientID]
,[ClientName]
,[ClientDOB]
FROM [dbo].[ClientModels] C
LEFT JOIN [DolsMcaClientModels] CI ON C.ClientID = CI.ClientID
WHERE CI.ClientID IS NULL
and list mandatory is missing.
SELECT I.[DolsMcaItemID]
,[DolsMcaItemName]
,[DolsMcaItemLevel]
,[DolsMcaItemMandatory]
,[DolsMcaItemStatus]
FROM [dbo].[DolsMcaItemModels] I
LEFT JOIN [DolsMcaClientModels] CI2 ON I.DolsMcaItemID = CI2.DolsMcaItemID
WHERE CI2.DolsMcaItemID IS NULL AND [DolsMcaItemMandatory] = 1
I don't know how to combine this together.