Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2004
    Posts
    7

    Unanswered: Finding Available Rentals

    Another need for SQL help:

    I need a query to find out all the available apartments. The tables, fields are:

    tAddress
    tUnits
    tTenant (ten_address, ten_unit, ten_move_date)

    What I've tried is this:
    SELECT tAddress.address, tUnits.unitsNumber
    FROM (tTenant INNER JOIN tAddress ON tTenant.ten_address = tAddress.addressID) INNER JOIN tUnits ON tTenant.ten_unit = tUnits.unitsID
    WHERE (((tTenant.ten_moved_date) Is Not Null));

    However, it pulls duplicate records because there are units in the tTenant table that have been rented out several times and the tenants have moved.

    I have tried doing an Exist SELECT 1 nested but not getting the results.

    TIA,
    Erik

  2. #2
    Join Date
    Feb 2004
    Posts
    4
    Use the keyword DISTINCT and I believe you will get the results you are looking for.


    SELECT DISTINCT tAddress.address, tUnits.unitsNumber
    FROM (tTenant INNER JOIN tAddress ON tTenant.ten_address = tAddress.addressID) INNER JOIN tUnits ON tTenant.ten_unit = tUnits.unitsID
    WHERE (((tTenant.ten_moved_date) Is Not Null));

  3. #3
    Join Date
    Feb 2004
    Posts
    7
    That still doesn't work... it still pulls up units that have tenants in them.

    Wouldn't this be a LEFT JOIN since it is kind of an "unmatched" query?

  4. #4
    Join Date
    Feb 2004
    Posts
    4
    /shrug. Your first post indicated you were getting extra data (duplicates) not incorrect data.

    I suspect your problem is that there are entries in the tenents table like this:

    Code:
    ten_address    ten_unit    ten_moved_date
        101                 b              01/01/2004
        101                 b
    indictating that you have rented out 101B, then it was unoccupied, now it is occupied. Unless there are some more columns in the table (such as a date rented, etc) that you could use for some fancy filtering, I suggest using a subquery to grab all the rows where moved_date IS NULL (ie occupied). Then use WHERE address, unit NOT IN (subquery) to grab all the other rows.

    Good Luck.

  5. #5
    Join Date
    Feb 2004
    Posts
    7
    Yes, you are correct it is pulling duplicate data. I do use a query that shows units that ARE rented:

    SELECT tTenant.ten_address, tTenant.ten_unit
    FROM tTenant
    WHERE tTenant.ten_moved_date Is Null;

    ...And other for all the addresses and units:

    SELECT tAddress.address, tUnits.unitsNumber
    FROM tAddress INNER JOIN tUnits ON tAddress.addressID = tUnits.UnitsAddressID;

    With what you are saying, how could this be done when the Address and Unit are in separate tables?

    Thanks for you help... I'm a little new SQL and learning a lot!

    Erik

  6. #6
    Join Date
    Feb 2004
    Posts
    4
    I'm guessing you have an autonumber for the primary key on the Tenant table.

    Try this and see what you get, I'm unsure if the syntax is perfect.

    Code:
    SELECT tAddress.address, tUnits.unitsNumber
    FROM (tTenant INNER JOIN tAddress ON tTenant.ten_address = tAddress.addressID) 
    INNER JOIN tUnits ON tTenant.ten_unit = tUnits.unitsID
    WHERE tTenant.Primary_Key NOT IN (SELECT tTenant.Primary_Key
    FROM tTenant
    WHERE tTenant.ten_moved_date Is Null)
    I'm pretty sure this will NOT return apartments that have never been rented (and are thus not in tTenant).

    You really need to try and normalize your database. For example, have a single table that includes every apartment, rented or not.

    Good Luck.

  7. #7
    Join Date
    Feb 2004
    Posts
    9
    I agree with James, your DB doesnt appear normalised

    You might want to consider a third table if u havent already got one.

    I use similar db that tracks DateIns and Outs. I need to report on Vacancies.

    Im able to bring up a list of all properties that are currently vacant by using an occupancy table ie tbl_Tenants_Property_bookings

    tblTenant
    TenantID
    Name
    etc

    tblBookings
    BookingID
    TenantID
    PropertyID
    DateIn
    DateOut


    tblProperty
    PropID
    Street
    etc

  8. #8
    Join Date
    Feb 2004
    Posts
    7
    There is a primary key in the tTenant table: ten_id

    This still pulls up all records with a moved date; so getting duplicate past rental units:

    SELECT tAddress.address, tUnits.unitsNumber
    FROM (tTenant INNER JOIN tAddress ON tTenant.ten_address = tAddress.addressID) INNER JOIN tUnits ON tTenant.ten_unit = tUnits.unitsID
    WHERE (tTenant.ten_id) Not In (SELECT tTenant.ten_id FROM tTenant WHERE tTenant.ten_moved_date Is Null)
    ORDER BY tAddress.address, tUnits.unitsNumber

    For example. 123 1A is not rented out right now.
    The query will list 123 1A five times because it has been rented out that many times

    The tables are this:

    tAddress = addressID (autonumber primary)
    address (text field of the address)

    tUnits = unitsID (autonumber primary)
    unitsNumber (text field of the unit number)
    unitsAddressID (number - for holding what address from tAddress this unit belongs to)


    What you are saying is that I should have a Table that show it as rented or not (like moving our current "tTenant.ten_moved_date" to a new Table such as: tOccupancy? Could work...... but there seems there has to be a way to get the available units as the database sits. In your example (blinky) you show a field of PropertyID... ours is a join on two tables to the property. If I create a new tOccupancy table, would I run into the same problem?

    I can pull a query to get:

    Apartments at are currently occupied
    AND
    All apartments in the entire system

    The properies we have are huge... there are 50 rental properites with about 50 to 250 units in each and some properties are homes where there are no units.

  9. #9
    Join Date
    Feb 2004
    Posts
    9
    Ok I see what u are saying. I had a similar problem becuase it would show up all the tenants for that property.

    This is the query that I use to know which properties are vacant

    SELECT tblProperties.Street, tblTenant_Property_Bookings.DateOut
    FROM tblTenants INNER JOIN (tblProperties INNER JOIN tblTenant_Property_Bookings ON tblProperties.PropertyID = tblTenant_Property_Bookings.PropertyID) ON tblTenants.TenantID = tblTenant_Property_Bookings.TenantID
    WHERE (((tblTenant_Property_Bookings.DateOut) Is Null));


    This doesnt give repeating tenants.

  10. #10
    Join Date
    Feb 2004
    Posts
    4
    The subquery:
    (SELECT tTenant.ten_id FROM tTenant WHERE tTenant.ten_moved_date Is Null)

    should be changed to something like this:

    SELECT tTenant.ten_address + ' ' + tTenant.ten_unit as complete_address WHERE tTenant.ten_moved_date Is Null

    That should create a single colum table that contains "123 1A", etc.

    Then adjust your main query the same way,

    SELECT tAddress.address + ' ' + tUnits.unitsNumber as complete_address
    FROM (tTenant INNER JOIN tAddress ON tTenant.ten_address = tAddress.addressID) INNER JOIN tUnits ON tTenant.ten_unit = tUnits.unitsID
    WHERE (complete_address) Not In (SELECT tTenant.ten_address + ' ' + tTenant.ten_unit as comp_address WHERE tTenant.ten_moved_date Is Null)


    We're just building uglier and uglier hacks to get around the structure of the database. I think you will still have problems when you get a brand new unit added into your system, since it will not appear in the tTenant table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •