FindChatroom.sql

/* 채팅방 정보 불러오기 */
SELECT [채팅방_정보], [채팅방_참여자_정보]
FROM (
    SELECT TOP(1) [채팅방_정보]
    FROM [채팅방_테이블] AS [채팅방]
    WHERE ((
        SELECT COUNT(*)
        FROM [채팅방_참여자_테이블] AS [참여자1]
        WHERE ([채팅방].[Id] = [참여자1].[채팅방Id]) AND (([참여자1].[챗봇Id] = '챗봇ID') OR ([참여자1].[사용자Id] = '사용자ID'))) = 2) AND ((
        SELECT COUNT(*)
        FROM [채팅방_참여자_테이블] AS [참여자2]
        WHERE [채팅방].[Id] = [참여자2].[채팅방Id]) = 2)
) AS [채팅방_정보]
LEFT JOIN (
    SELECT [참여자3_정보], [참여자3_챗봇정보]
    FROM [채팅방_참여자_테이블] AS [참여자3]
    LEFT JOIN [챗봇_테이블] AS [참여자3_챗봇정보] ON [참여자3].[챗봇Id] = [참여자3_챗봇정보].[Id]
) AS [참여자3_정보] ON [채팅방_정보].[Id] = [참여자3_정보].[채팅방Id]
ORDER BY [채팅방_정보].[Id], [참여자3_정보].[Id], [참여자3_챗봇정보].[Id]