select statement needed

drummer79 · #1 (**permalink**) 10-03-09, 04:18

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.

mike_bike_kite · #2 (**permalink**) 10-03-09, 04:41

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

drummer79 · #3 (**permalink**) 10-03-09, 04:44

Thank you. I will try this.

drummer79 · #4 (**permalink**) 10-03-09, 05:03

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.

dav1mo · #5 (**permalink**) 10-05-09, 08:39

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

drummer79 · #6 (**permalink**) 10-06-09, 12:28

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.

mike_bike_kite · #7 (**permalink**) 10-06-09, 14:01

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.

r937 · #8 (**permalink**) 10-06-09, 14:39

4.1.22 supports subqueries

drummer79 · #9 (**permalink**) 10-11-09, 19:04

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.

mnirwan · #10 (**permalink**) 10-12-09, 01:46

Looks like homework assignment.

Internet Services

Web Development

Digital Marketing

Consumer Tech