mrroach og
#611
- 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;