--
-- ### Advanced outer join check
-- 
-- 
--

drop if exists table sysmt_threadgroup;
create table sysmt_threadgroup ( primary tgid int not null, tgname string(10) );

drop if exists table sysmt_user;
create table sysmt_user ( primary uid int not null, uname string(10) );

drop if exists table sysmt_thread;
create table sysmt_thread ( primary thrid int not null, tgid int, bgid int, shortdesc string(10), status string(10), remind datetime);

drop if exists table sysmt_threadstat;
create table sysmt_threadstat ( thrid int not null, uid int not null, readstat string(10) );

create primary index on sysmt_threadstat( thrid, uid );

insert into sysmt_user values ( 100, 'Lemke');
insert into sysmt_user values ( 101, 'Kuno');
insert into sysmt_user values ( 102, 'Berta');

insert into sysmt_threadgroup values ( 200, 'MyTG');
insert into sysmt_threadgroup values ( 201, 'YourTG');

insert into sysmt_thread values ( 300, 200, 400, 'My Thread', 'OPEN', sysdate);
insert into sysmt_thread values ( 301, 200, 401, 'Your Thread', 'OPEN', sysdate);
insert into sysmt_thread values ( 302, 201, 401, 'His Thread', 'OPEN', sysdate);

insert into sysmt_threadstat values ( 300, 100, 'READ');
insert into sysmt_threadstat values ( 302, 100, 'READ');


drop if exists view sysmt_threadview;
create view sysmt_threadview as
select
 t.thrid as thrid,
 tg.tgname as tgname,
 tg.tgid as tgid,
 t.shortdesc as shortdesc,
 t.status as status, 
u.uid as uid,
case when rs.readstat = null then 'UNREAD' else 'READ' end as readstat,
rs.uid as rsuid
from
 sysmt_thread t inner join sysmt_threadgroup tg on t.tgid = tg.tgid
inner join sysmt_user u on u.uid = u.uid
left outer join sysmt_threadstat rs on rs.thrid = t.thrid and rs.uid = u.uid;

select * from sysmt_threadstat where thrid = 302 and uid = 100;

select * from sysmt_threadview;
select * from sysmt_threadview where uid = 100;
select * from sysmt_threadview where status = 'OPEN';


select * from sysmt_threadview where uid = 100;
