sqll_query

I have 3 tables. attached file have DDL for creating tables and loading data.

 book_review
(bkno    number(10),
 stage    number(1),
 status   varchar2(1),
 created_date   date );
 
 
 param
 (no_rows  number(3) )
    /
 book_assign
 (  assign_no  number(10),
    bk_no      number(10),
    assign_to  varchar2(10),
    created_date date )


I want to create a view/query that displays all the books that have:

1.  Last stage = 0 and status = 'A' sorted by created date

and then pick the number of rows from this list based on param.no_rows.

I wrote this which seems to work.


select * from
(
select * from book_review where (bkno,created_Date) in
(
select bkno,max(created_date) from book_review group by bkno
)
and status = 'A' and stage=0
order by created_Date
)
where rownum <= (select no_rows from param);



Now i need to add two columns to result set above as follows:

COLUMN ONE:

--Find Previous assignment (PREV_ASSIGN)
--Not assigned = NULL = no records in book_assign table
--Assigned = Show last record for assignment in book assign_table

I think this query gives me this:

select assign_to from book_assign Prev_assign where (bk_no,created_date) in
(select bk_no,max(created_date) from book_assign group by bkno)
/

COLUMN TWO:

--Find current assignment (CUR_ASSIGN)
--Not assigned = Null = no records in book_assign table
--There is a record in "book_assign" but book_assign.created_Date < book_review.created_Date
-- show NULL value
--There is a record in "book_assign" but book_assign.created_Date >= book_review.created_Date
-- show userid

I thnk query should look like this.

select assign_to from book_assign Prev_assign where (bk_no,created_date) in
(select bk_no,max(created_date) from book_assign group by bkno)
and created_date >= select b.created_date from book_review where b.created_Date >=
(the created_date in booK_review record).
/


Do you know to merge the last two queries into the main one? Would you include it as part of the column select of the main one like :
select *,query1,query2 from table.

book-review.txt