테이블 구조를 건드릴 순 없나요?

별로 생각해 보진 않았지만, 테이블 구조를 바꾸면 query가 간단해 질 것 같은데..

그럼..

이하 샘플입니다..

create table member(
m_no char(8) not null,
m_name varchar(10) not null,
m_email varchar(20) not null,
m_password char(20) not null,
constraint pk_member_m_no primary key (m_no)
)
go

insert into member values ('MNO00001', 'test name1', 'test e-mail1', 'must be encrypted!!!')
insert into member values ('MNO00002', 'test name2', 'test e-mail2', 'must be encrypted!!!')
insert into member values ('MNO00003', 'test name3', 'test e-mail3', 'must be encrypted!!!')
go

create table command(
com_no char(10) not null,
com_date datetime not null,
com_note varchar(400),
com_m_no char(8) not null,
constraint fk_command_d_com_m_no foreign key (com_m_no) references member (m_no),
constraint pk_command_d_com_no primary key (com_no)
)
go

insert into command values ('CNO00001', getdate(), 'command1 description for test!!!', 'MNO00001')
insert into command values ('CNO00002', dateadd(day, -1, getdate()), 'command2 description for test!!!', 'MNO00001')
insert into command values ('CNO00003', dateadd(day, -2, getdate()), 'command3 description for test!!!', 'MNO00002')
insert into command values ('CNO00004', dateadd(day, -3, getdate()), 'command4 description for test!!!', 'MNO00002')
insert into command values ('CNO00005', dateadd(day, -4, getdate()), 'command5 description for test!!!', 'MNO00002')
go

select * from member
select * from command
go

create view member_view_left_join as
select m.m_no, m.m_name, m.m_email, c.com_note as m_note, c.com_date as m_change_date
  from member as m
          left join            -- member를 기준으로 데이터를 추출할 때..
--          inner join         -- 양쪽 모두 존재하는 데이터만 원할 때..
          (select c1.com_m_no, c1.com_date, c2.com_note
              from (select com_m_no, max(com_date) as com_date
                          from command
                          where com_date < getdate()    -- 참고 : getdate()는 현재시각임..
                          group by com_m_no) c1
                      left join
                      (select com_m_no, com_date, com_note
                           from command) c2
                      on c1.com_m_no = c2.com_m_no
                     and c1.com_date = c2.com_date) c
          on m.m_no = c.com_m_no
go

select * from member_view_left_join
go

drop view member_view_left_join
drop table command
drop table member