get data from sqlite limited on inner join

Question

I want to get rooms limited to 20 with last message only order by lastMessage.created_at DESC

message table

     create table tb_message (
            message_id varchar(50) primary key  ,
            room_id varchar(50) not null,
            message_type text not null,
            message text not null,
            sender_id varchar(50) not null,
            recipient_id varchar(50) not null,
            reply_id varchar(50) default null ,
            created_at BIGINT ,
            status integer ,
            constraint tb_message_chat_id_fk foreign key (room_id) references tb_room (room_id),
            constraint tb_message_sender_user_fk foreign key (sender_id) references tb_user (user_id),
            constraint tb_message_recipient_user_fk foreign key (recipient_id) references tb_user (user_id))

rooms table

          create table tb_room (
            room_id  varchar(50) primary key ,
            group_id varchar(50),
            room_type text ,
            owner_id varchar(50) not null ,
            is_muted integer default 0,
             constraint tb_chat_user_id_fk foreign key (owner_id) references tb_user (user_id))

user table

      create table tb_user (
            user_id varchar(50) primary key ,
            user_name text not null,
            online_status integer )

my query is

      SELECT *
      FROM  (select * from tb_room limit 20) as room
      
      INNER JOIN tb_message 
        ON room.room_id = (select room_id from tb_message   limit 1)  
        
      INNER JOIN tb_user
        ON tb_user.user_id = room.owner_id
      ORDER BY tb_message.created_at DESC

it return all room with its all messages

see this ans please https://stackoverflow.com/a/5126818/9294077 — Hatem R Saber, Dec 30 '20 at 21:41
the condition in the `ON` clause needs to relate the two tables being joined. A subquery in the `ON` clause is not correct. — Barmar, Dec 30 '20 at 21:41
That answer makes the same mistake. Unless you don't care which room you select. — Barmar, Dec 30 '20 at 21:42
It's not clear what you're actually trying to do. Do you want the last message for 20 rooms, or the last 20 messages for each room? — Barmar, Dec 30 '20 at 21:44
If you're using MySQL 8.x or SQLite you should be able to do it with a window function. — Barmar, Dec 30 '20 at 21:46
I need 20 rooms with 1 message for each room, not all messages only one last message for each room I use Sqlite — Hatem R Saber, Dec 30 '20 at 21:48

get data from sqlite limited on inner join

0 Answers0