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.
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.
select unit_id, unit_number, 'vacant'
from unit u
where not exists(
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).
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.
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 (126.96.36.199. Rewriting Subqueries as Joins for Earlier MySQL Versions). Thanks for the help. You pointed me in the right direction.
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.
FROM renter r
MAX(date_time) AS date_time,r2.unit_id,
JOIN unit ON unit.unit_id = r2.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.