I have 4 tables, I'm trying to get all the chats that belong to the user with the id of 1, I can do that shown in the query below however I'm unsure of how efficient it is.
Secondly, I want to make this query a bit more complex by also getting all the other users which are in the chats along with the user that has the id of 1 however I'm unsure how to do this.
select * from chat_users left join chats on chat_users.chat_id = chats.id left join users on chat_users.user_id = users.id where user_id = 1
Users - id, username
Chats - id, chatname
chat_users id, user_id, chat_id
Posts - post, id, text
E.g.
If we imagine chats table as:
id : 1 | chatname : FirstChat
Chat_users as
id : 1 | user_id : 1 | chat_id : 1
id : 2 | user_id : 2 | chat_id : 1
Users as
id: 1 | username: firstUser
id: 2 | username: secondUser
I eventually want to end up with a query that returns all the chats that user1 is involved in along with the data for the other users in them chats however I'm not sure how I would do that?
Here is one way to phrase the query:
select c.*, u.*
from chat_users cu
inner join chats c on c.id = cu.chat_id
inner join users u on u.id = cu.user_id
where exists (
select 1 from chat_users cu1 where cu1.chat_id = cu.chat_id and cu1.user_id = 1
)