0

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.

AliAzra
  • 889
  • 1
  • 9
  • 28
  • So are you asking how to write these SQL in terms of LINQ? – Selim Yildiz Oct 07 '19 at 19:22
  • Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. – NetMage Oct 07 '19 at 19:37

0 Answers0