-5

Possible Duplicate:
MySQL SELECT results from 1 table, but exclude results depending on another table?

I hope I'm not asking something already answered..didn't see one. Seems simple but I can't get it to work.

I have two tables: Unit and Lease.

I want a list of all open Units (building and unit unitnumber) everything from the unit table excluding those units in the lease table that have a null in the enddate field.

Structure for tables (i removed other fields not necessary for this example:

CREATE  TABLE Unit(
    UnitKey     Int NOT NULL AUTO_INCREMENT,
    UnitNumber  Char(5) NOT NULL,
    BuildingKey Int     NOT NULL,
    CONSTRAINT   UNIT_PK    PRIMARY KEY(UnitKey),
    CONSTRAINT   UNIT_BLDG_FK     FOREIGN KEY(BuildingKey)
                    REFERENCES Building(BuildingKey));

CREATE  TABLE Lease(
    LeaseKey  Int                   NOT NULL AUTO_INCREMENT,
    UnitKey   Int                  NOT NULL,
    EndDate     Date                   NULL,
    CONSTRAINT LEASE_PK               PRIMARY KEY(LeaseKey),
    CONSTRAINT      LEASE_UNIT_FK   FOREIGN KEY(UnitKey) REFERENCES Unit(UnitKey));
Community
  • 1
  • 1
C. Stanford
  • 1
  • 2
    Similar to: http://stackoverflow.com/questions/4186242/sql-select-all-unique-values-in-table-a-which-are-not-in-table-b and http://stackoverflow.com/questions/4660871/mysql-select-all-items-from-table-a-if-not-exist-in-table-b – Chalise Aug 15 '12 at 17:37
  • 2
    your question has already been answered many times: http://stackoverflow.com/questions/2862778/mysql-select-results-from-1-table-but-exclude-results-depending-on-another-tabl?rq=1 http://stackoverflow.com/questions/7596073/select-from-one-table-where-not-in-another?rq=1 http://stackoverflow.com/questions/10255266/select-users-from-one-table-only-if-not-in-another?rq=1 – Jocelyn Aug 15 '12 at 17:41

1 Answers1

1

Try this:

select u.*
from units u
where not exists (select 1 from lease l where l.unitkey = u.unitkey and l.enddate is null)

In other database engines, you would use "not in". However, this optimizes better in mysql.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786