mrroach og

#611
Raw
Created
Dec. 14, 2022, 12:16 p.m.
Expires
Never
Size
1.0 KB
Hits
155
Syntax
SQL
Private
✗ No
# create tables
create table posts(
  id int,
  thread_id int,
  message_raw varchar(45),
  is_top_post int
);
create table threads(
  id int,
  board_id int
);
create table boards(
  id int,
  dir varchar(40)
);

insert into boards(id, dir) values(1, 'test');
insert into boards(id, dir) values(2, 'test2');
insert into threads(id, board_id) values(1,1);
insert into posts(id, thread_id, message_raw, is_top_post) values(1,1,'p1t1', 1);
insert into posts(id, thread_id, message_raw, is_top_post) values(2,1,'p2t1', 0);
insert into threads(id, board_id) values(2,1);
insert into posts(id, thread_id, message_raw, is_top_post) values(3,2,'p3t2', 1);
insert into threads(id, board_id) values(3,2);
insert into posts(id, thread_id, message_raw, is_top_post) values(4,3,'p4t3', 1);


# read
select OG.id, OG.thread_id, message_raw,
  (select dir from boards where id = (
    select board_id from threads where id = posts.thread_id)
  ) as dir,
(
  select id from posts where is_top_post = 1 and thread_id = threadid
) AS top_post
from posts AS OG;