Q

Basic three-table join with a condition

I have to join three tables: the Rental table, that has the First and Last Name of the Renter, the Rental Unit table that has the Rental Address, and the Active Rentals table that has the Start Date and Length. The information must come from the Active Rentals table. Only active rentals can be listed. Also I have to display all units with an ocean view. These come from Yes/No fields. I want to use True and False as criterion.

Let's take the active rentals first. You said the Active Rentals table has Start Date and Length. I will assume that Length is in days, and that an "active" rental is one where the Start Date is less than today, and if you added Length days, it would make the duration of the rental go from the Start Date to at least today, or beyond today into the future. The join is the easy part.

select U.RentalAddress
     , R.FirstName
     , R.LastName
     , A.StartDate
     , A.Length
  from ActiveRentals A 
inner
  join RentalUnit U
    on A.UnitID = U.UnitID 
inner
  join Rental R
    on A.RentalID = R.RentalID
 where A.StartDate + Length days
    >= current_date

You will probably have to change this syntax to match your particular database. For example, in Microsoft Access, you would need to parenthesize one of the inner joins and change the WHERE condition to

 where DATEADD("d",Length,StartDate) >= DATE()

For your second question, units with an ocean view,

select RentalAddress
  from RentalUnit 
 where OceanView <> 0

For more on Yes/No fields, see Boolean Columns.


This was first published in January 2003

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close