Excluding results in one table from a SELECT * in another? [duplicate]

Question

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));

Similar to: stackoverflow.com/questions/4186242/… and stackoverflow.com/questions/4660871/… — Chalise
– Chalise, Commented Aug 15, 2012 at 17:37
your question has already been answered many times: stackoverflow.com/questions/2862778/… stackoverflow.com/questions/7596073/… stackoverflow.com/questions/10255266/… — Jocelyn
– Jocelyn, Commented Aug 15, 2012 at 17:41

Gordon Linoff · Accepted Answer · 2012-08-15 17:37:57Z

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.

Collectives™ on Stack Overflow

Excluding results in one table from a SELECT * in another? [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related