Selecting two columns gives duplicates in Postgres-CodePudding

I have the following structure:

ChatMessages table:

  Column   |            Type             | Collation | Nullable | Default
----------- ----------------------------- ----------- ---------- ---------
 Id        | text                        |           | not null |
 SenderId  | text                        |           |          |
 Message   | text                        |           |          |
 ChatId    | text                        |           |          |
 CreatedAt | timestamp without time zone |           | not null |
Indexes:
    "PK_ChatMessages" PRIMARY KEY, btree ("Id")
    "IX_ChatMessages_ChatId" btree ("ChatId")
    "IX_ChatMessages_SenderId" btree ("SenderId")
Foreign-key constraints:
    "FK_ChatMessages_ChatUsers_SenderId" FOREIGN KEY ("SenderId") REFERENCES "ChatUsers"("Id") ON DELETE RESTRICT
    "FK_ChatMessages_Chats_ChatId" FOREIGN KEY ("ChatId") REFERENCES "Chats"("Id") ON DELETE RESTRICT

ChatUsers table:

  Column   |            Type             | Collation | Nullable | Default
----------- ----------------------------- ----------- ---------- ---------
 Id        | text                        |           | not null |
 Username  | text                        |           |          |
 Email     | text                        |           |          |
 CreatedAt | timestamp without time zone |           | not null |
Indexes:
    "PK_ChatUsers" PRIMARY KEY, btree ("Id")
Referenced by:
    TABLE ""ChatMessages"" CONSTRAINT "FK_ChatMessages_ChatUsers_SenderId" FOREIGN KEY ("SenderId") REFERENCES "ChatUsers"("Id") ON DELETE RESTRICT
    TABLE ""Chats"" CONSTRAINT "FK_Chats_ChatUsers_User1Id" FOREIGN KEY ("User1Id") REFERENCES "ChatUsers"("Id") ON DELETE RESTRICT
    TABLE ""Chats"" CONSTRAINT "FK_Chats_ChatUsers_User2Id" FOREIGN KEY ("User2Id") REFERENCES "ChatUsers"("Id") ON DELETE RESTRICT

If I execute select "SenderId", "Email", "Message" from "ChatMessages", "ChatUsers"; I receive a list with all messages, however each message is multiplied by the amount of users' email addresses:

               SenderId               |        Email        |                        Message
-------------------------------------- --------------------- -------------------------------------------------------
 2f5aca90-0599-400c-8455-31a22c1d4dcd | user1@example.com | This is a test message.
 2f5aca90-0599-400c-8455-31a22c1d4dcd | user1@example.com | This is also a test message.
 b3e218e6-dd41-4223-978b-a54f46fc465e | user1@example.com | okay ...
 2f5aca90-0599-400c-8455-31a22c1d4dcd | user2@example.com | This is a test message.
 2f5aca90-0599-400c-8455-31a22c1d4dcd | user2@example.com | This is also a test message.
 b3e218e6-dd41-4223-978b-a54f46fc465e | user2@example.com | okay ...

However, I want it to be displayed like the following:

               SenderId               |        Email        |                        Message
-------------------------------------- --------------------- -------------------------------------------------------
 2f5aca90-0599-400c-8455-31a22c1d4dcd | user1@example.com   | This is a test message.
 2f5aca90-0599-400c-8455-31a22c1d4dcd | user1@example.com   | This is also a test message.
 b3e218e6-dd41-4223-978b-a54f46fc465e | user2@example.com   | okay ...

What would be the correct query for that?

CodePudding user response：

try with this (postgres syntax):

SELECT cm.SenderId, cu.Email, cm.Message FROM ChatMessages cm JOIN ChatUsers cu ON cm.SenderId = cu.Id