MSSQL: How to join only if its children not exist-CodePudding

I want to left join but only if it does not have the specific child.

Here is my current query:

SELECT "chatRoom"."id" as id, 
       "chatRoom"."name" as name, 
       "chatRoom"."type" as type, 
       "chatRoom"."description" as description, 
       "chatRoom"."thumbnail" as thumbnail, 
       "chatRoom"."status" as status, 
       chats.[unreadCount] as unreadCount
FROM "chat_room" "chatRoom" 
LEFT JOIN "chat_room_participant" "participants" ON "participants"."chatRoomId"="chatRoom"."id"  
LEFT JOIN (
    SELECT "chatRoom"."id" AS "chatRoomId", COUNT(readBy.id) AS "unreadCount"
    FROM "chat" "chat" 
    LEFT JOIN "chat_room" "chatRoom" ON "chatRoom"."id"="chat"."chatRoomId"  
    LEFT JOIN "chat_read_by_chat_room_participant" "chat_readBy" ON "chat_readBy"."chatId"="chat"."id" 
    LEFT JOIN "chat_room_participant" "readBy" ON "readBy"."id"="chat_readBy"."chatRoomParticipantId" 
    WHERE NOT(readBy.UserId IN ('ca774a5f-a04d-ec11-ae58-74d83e04f9d3'))
       OR "readBy"."id" IS NULL
    GROUP BY "chatRoom"."id"
) "chats" ON chats.chatRoomId = "chatRoom"."id" 
WHERE ('ALL' = 'ALL' OR "chatRoom"."status" = 'ALL')
  AND "chatRoom"."applicationId" = '4ac752e9-004c-ec11-ae53-74d83e04f9d3'
  AND "participants"."userId" = 'A97D66C4-014C-EC11-AE53-74D83E04F9D3'
ORDER BY "chatRoom"."lastUpdate" DESC

In this query, it returns the correct value only if the readBy is null or contains 1 entry which equals to the given userId.

So here I have the userId. I want to get all the unread chats count from each chatRoom that has the user as a participant.

The schema is like this:

chatRoom has many chats
chatRoom has many participants

chats has many to many `readBy` (participants)

So there is a column automatically created for the many-to-many relation:

column: chat_read_by_chat_room_participant

Contains:

|chatId|chatRoomParticipantId|

In my query above, the left join will get any readBy from another user:

WHERE NOT(readBy.UserId IN ('ca774a5f-a04d-ec11-ae58-74d83e04f9d3')) OR "readBy"."id" IS NULL GROUP BY "chatRoom"."id") "chats" ON chats.chatRoomId = "chatRoom"."id"

which I do not want. This will return the entry if the user already read the chat, but another users have read as well. I want to return the entry only if the user has not read the chat.

How can I do this?

CREATION QUERY

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[chat](
    [id] [uniqueidentifier] NOT NULL,
    [sentAt] [bigint] NOT NULL,
    [type] [nvarchar](255) NOT NULL,
    [status] [nvarchar](255) NOT NULL,
    [message] [nvarchar](max) NOT NULL,
    [filePath] [nvarchar](max) NULL,
    [chatRoomId] [uniqueidentifier] NOT NULL,
    [senderId] [nvarchar](255) NOT NULL,
    [userId] [uniqueidentifier] NULL,
 CONSTRAINT [PK_9d0b2ba74336710fd31154738a5] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[chat] ADD  CONSTRAINT [DF_9d0b2ba74336710fd31154738a5]  DEFAULT (newsequentialid()) FOR [id]
GO

ALTER TABLE [dbo].[chat]  WITH CHECK ADD  CONSTRAINT [FK_52af74c7484586ef4bdfd8e4dbb] FOREIGN KEY([userId])
REFERENCES [dbo].[chat_room_participant] ([id])
GO

ALTER TABLE [dbo].[chat] CHECK CONSTRAINT [FK_52af74c7484586ef4bdfd8e4dbb]
GO

ALTER TABLE [dbo].[chat]  WITH CHECK ADD  CONSTRAINT [FK_e49029a11d5d42ae8a5dd9919a2] FOREIGN KEY([chatRoomId])
REFERENCES [dbo].[chat_room] ([id])
GO

ALTER TABLE [dbo].[chat] CHECK CONSTRAINT [FK_e49029a11d5d42ae8a5dd9919a2]
GO

CREATE TABLE [dbo].[chat_read_by_chat_room_participant](
    [chatId] [uniqueidentifier] NOT NULL,
    [chatRoomParticipantId] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_f3dd24628d4644dd6e79bcd03d1] PRIMARY KEY CLUSTERED 
(
    [chatId] ASC,
    [chatRoomParticipantId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[chat_read_by_chat_room_participant]  WITH CHECK ADD  CONSTRAINT [FK_011624ccd7e7b0281ef629f2930] FOREIGN KEY([chatId])
REFERENCES [dbo].[chat] ([id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[chat_read_by_chat_room_participant] CHECK CONSTRAINT [FK_011624ccd7e7b0281ef629f2930]
GO

ALTER TABLE [dbo].[chat_read_by_chat_room_participant]  WITH CHECK ADD  CONSTRAINT [FK_2e33e3de9d7c91d426c09a24810] FOREIGN KEY([chatRoomParticipantId])
REFERENCES [dbo].[chat_room_participant] ([id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[chat_read_by_chat_room_participant] CHECK CONSTRAINT [FK_2e33e3de9d7c91d426c09a24810]
GO

CREATE TABLE [dbo].[chat_room](
    [id] [uniqueidentifier] NOT NULL,
    [name] [nvarchar](255) NULL,
    [type] [nvarchar](255) NOT NULL,
    [thumbnail] [nvarchar](max) NULL,
    [description] [nvarchar](max) NULL,
    [status] [nvarchar](255) NOT NULL,
    [applicationId] [uniqueidentifier] NOT NULL,
    [lastUpdate] [bigint] NULL,
 CONSTRAINT [PK_8aa3a52cf74c96469f0ef9fbe3e] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[chat_room] ADD  CONSTRAINT [DF_8aa3a52cf74c96469f0ef9fbe3e]  DEFAULT (newsequentialid()) FOR [id]
GO

ALTER TABLE [dbo].[chat_room]  WITH CHECK ADD  CONSTRAINT [FK_2226638e6b7665ec0259d246b2b] FOREIGN KEY([applicationId])
REFERENCES [dbo].[application] ([id])
GO

ALTER TABLE [dbo].[chat_room] CHECK CONSTRAINT [FK_2226638e6b7665ec0259d246b2b]
GO

CREATE TABLE [dbo].[chat_room_participant](
    [id] [uniqueidentifier] NOT NULL,
    [joinedAt] [bigint] NOT NULL,
    [privilege] [nvarchar](255) NOT NULL,
    [chatRoomId] [uniqueidentifier] NOT NULL,
    [userId] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_15913cf37a762fce4c8d6a32a42] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [UQ_ae9630d66f6c5d12afd1a991fec] UNIQUE NONCLUSTERED 
(
    [chatRoomId] ASC,
    [userId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[chat_room_participant] ADD  CONSTRAINT [DF_15913cf37a762fce4c8d6a32a42]  DEFAULT (newsequentialid()) FOR [id]
GO

ALTER TABLE [dbo].[chat_room_participant]  WITH CHECK ADD  CONSTRAINT [FK_9f718459ea81b5130f81980ca08] FOREIGN KEY([userId])
REFERENCES [dbo].[user] ([id])
GO

ALTER TABLE [dbo].[chat_room_participant] CHECK CONSTRAINT [FK_9f718459ea81b5130f81980ca08]
GO

ALTER TABLE [dbo].[chat_room_participant]  WITH CHECK ADD  CONSTRAINT [FK_fb664f48a4ec615ec5cce90a25d] FOREIGN KEY([chatRoomId])
REFERENCES [dbo].[chat_room] ([id])
GO

ALTER TABLE [dbo].[chat_room_participant] CHECK CONSTRAINT [FK_fb664f48a4ec615ec5cce90a25d]
GO

CodePudding user response：

It seems that what you want is an anti-semi join. That is, a join to demonstrate that a row does not exist.

The two typical methods are...

  main_table    AS m
LEFT JOIN
  other_table   AS o
    ON  o.m_id = m.id
    AND o.user = 'xyz'
WHERE
  o.id IS NULL

Or...

  main_table   AS m
WHERE
  NOT EXISTS (
    SELECT *
      FROM other_table   AS o
     WHERE o.m_id = m.id
       AND o.user = 'xyz'
  )

Exactly how to apply this to your example is unclear as you have cluttered your question with too many other details. (It is not a Minimal Verifiable Example.)

CodePudding user response：

It looks like you just need a WHERE NOT EXISTS correlated subquery.

Don't be tempted to just use LEFT JOIN IS NULL syntax, it is generally less efficient, as it hides from the optimizer that you are doing an anti-join. Occasionally it can be useful though.

Further notes:

Don't quote column and table names unless you have to. And generally avoid names that need quoting.
Don't alias columns that don't need aliasing.
Choose short meaningful table aliases.
Basic formatting and good use of whitespace helps readability
Your LEFT JOIN chat_room_participant logically becomes an INNER JOIN because of the WHERE.
You don't need to re-join chat_room in the grouped subquery, you can just group by the join column.
You may want to use a grouped OUTER APPLY instead of that subquery. It is unlikely to get a different query plan, but it can be easier to read.
COUNT(SomeNotNullValue) is the same as COUNT(*)
('ALL' = 'ALL' OR cr.status = 'ALL') only makes sense if cr.Status is nullable, otherwise you would just use cr.status = 'ALL'. Even if it is nullable, you may as well use (cr.Status IS NULL OR cr.status = 'ALL')

SELECT
  cr.id,
  cr.name, 
  cr.type, 
  cr.description, 
  cr.thumbnail, 
  cr.status, 
  ch.unreadCount
FROM chat_room cr
JOIN chat_room_participant p ON p.chatRoomId = cr.id
LEFT JOIN (
    SELECT
      c.chatRoomId AS chatRoomId,
      COUNT(*) AS unreadCount
    FROM chat c
    WHERE NOT EXISTS (SELECT 1
        FROM chat_read_by_chat_room_participant chat_readBy
        JOIN chat_room_participant readBy ON readBy.id = chat_readBy.chatRoomParticipantId
        WHERE chat_readBy.chatId = c.id
          AND readBy.UserId IN ('ca774a5f-a04d-ec11-ae58-74d83e04f9d3')
    )
    GROUP BY c.chatRoomId
) c ON c.chatRoomId = cr.id
WHERE (cr.Status IS NULL OR cr.status = 'ALL')
  AND cr.applicationId = '4ac752e9-004c-ec11-ae53-74d83e04f9d3'
  AND p.userId = 'A97D66C4-014C-EC11-AE53-74D83E04F9D3'
ORDER BY
  cr.lastUpdate DESC;