Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2009
    Posts
    8

    Unanswered: select statement needed

    I have two tables in a database call apartment. One name unit and the other renter. If the unit is empty, vacant is placed in the renter name field else the renter name is placed in the renter field. Each unit could have multiple renter.

    apartment

    unit
    -unit_id
    -number

    renter
    -rent_id
    -unit_id
    -name

    I am trying to find the units that are vacant. The renter table must be in DECS order to find the lastest renter in that unit.


    unit_id-----unit number------renter
    1 24 vacant
    3 35 vacant
    56 45 vacant

    I am trying to write the SELECT statement that would pull this inform from the database with no luck at all. Thank you in advance.

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Perhaps this might work:
    Code:
    select unit_id, unit_number, 'vacant'
    from   unit u
    where  not exists(
               select  1
               from    renter r
               where   r.unit_id = u.unit_id
                       and r.name != 'vacant' )
    Your design has a few issues - better to not store 'vacant' in the renter table as then you could easily tell by the absence of any renter records for a unit that the unit is vacant. Having a vacant record means you have to remove that record each time you add a new renter. Shouldn't there be a name on the Unit table? You can't rely on the renter table to be in any order as the records can be stored in any order (in practise this changes with the indexing).

    Mike

  3. #3
    Join Date
    Oct 2009
    Posts
    8
    Thank you. I will try this.

  4. #4
    Join Date
    Oct 2009
    Posts
    8
    The renter table is datetime stamped. The datebase is setup to search for past renters in a particular unit. The reason I put vacant in the renter field when the unit is empty, I can always search the renter table for vacant for a particular unit and it wll tell me the date and time the unit was empty and how long it was empty. There are other ways of doing the same thing. There is a name in the unit table. I didn't think it was necessary to display it in the example. Thank you again for the quick response.

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    You left out the part about timestamp in first post, so how about something like:
    Code:
    select unit from table t1
    where col1 = 'vacant'
      and ts_col = (select max(ts_col) from table t2
                         where t1.unit = t2.unit)
    Dave Nance

  6. #6
    Join Date
    Oct 2009
    Posts
    8

    Select statement

    The two above examples did not work because the version of mysql (4.1.22) I am using does not support subquery. I will probably have to use the joins statement per the msysql manual (12.2.8.11. Rewriting Subqueries as Joins for Earlier MySQL Versions). Thanks for the help. You pointed me in the right direction.

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I'd suggest when posting questions in future that it might be a good idea to provide the table definitions and just inform folks that you're using an old version of MySQL - you'll get a correct answer quicker.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    4.1.22 supports subqueries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Oct 2009
    Posts
    8

    select statement

    Code:
    SELECT r2.unit_id,r2.number,r.name
        FROM renter r
           JOIN (
              SELECT
                 MAX(date_time) AS date_time,r2.unit_id,
                         r2.name,unit.unit_id,unit.number
              FROM
                 renter r2
                 JOIN unit ON unit.unit_id = r2.unit_id
              GROUP BY
                 unit.unit_id
           ) AS r2 ON r.unit_id = r2.unit_id
              AND r.date_time = r2.date_time
              AND r.name = 'Vacant'
    This is what I used to solve my issue. I located it in anther portion of this forum. It locate all the vacant units in the apartment database. Thanks for the help. Issue solved.

  10. #10
    Join Date
    Sep 2009
    Posts
    64
    Looks like homework assignment.

Posting Permissions

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