Comibing results from two tables 1

[aravindk]

I need to join two tables where when the join condition is true, it should return YES and returns NO if it doesnt. Let me try to give an example:

I have BOOK_RENTALS which contains customer names and books:

JOHN SMITH, BOOKA
JOHN SMITH, BOOKB
JOHN SMITH, BOOKC

and I have a BOOK_MASTER:

BOOKA
BOOKB
BOOKC
BOOKD
BOOKE

I would like to combine the results from these two tables such that I get results as follows:

JOHN SMITH, BOOKA, YES
JOHN SMITH, BOOKB, YES
JOHN SMITH, BOOKC, YES
JOHN SMITH, BOOKD, NO
JOHN SMITH, BOOKE, NO

Is this possible in a single sql statement? Also pl note that the BOOK_RENTALS is potentially a large table with over 1 million records and BOOK_MASTER is a small table with about 400 records.

Thanks.

Any help is appreciated.

 

[r937]

select C.customer_name
     , M.book_name
     , case when R.customer_name is null
            then 'NO' else 'YES' end   as joined 
  from BOOK_MASTER
cross
  join ( select distinct customer_name 
          from BOOK_RENTALS ) as C
left outer
  join BOOK_RENTALS as R
    on R.customer_name = C.customer_name
   and R.book_name = M.book_name
order
    by C.customer_name
     , M.book_name

if you had a table for customers, the SELECT DISTINCT from BOOK_RENTALS would not be necessary

r937.com | rudy.ca

 

[aravindk]

Looks like I can actually make this work. Thanks a lot!