Query multiple values in a single column in a many to one relationship

[cravincreeks]

Hello all,

I'm stuck on this query. I have a Parcels table which has a record for every parcel such as

ParcelID
--------
Parcel1
Parcel2
Parcel3
Parcel4

I also have a land use table that relates to the parcel via the ParcelID. A single parcel can have many land uses.

ParcelID        LandUse
--------        ----------
Parcel1         Industrial
Parcel1         Commercial 
Parcel1         Office
Parcel2         Commercial
Parcel2         Office
Parcel3         Commercial 
Parcel3         Office

I need to query for all parcels that have an Industrial AND Commercial land use. In the example above, Parcel1, and only Parcel1, should be returned. If I query for WHERE LandUse = 'Industrial' and LandUse = 'Commercial', zero records are returned. If I put an OR in the Where clause, I'd get Parcels 1,2, AND 3, which is not what I need.

How do I get what I need?

Thanks

 

[Golom]

Select P.ParcelID

From Parcels P INNER JOIN LandUse U
     ON P.ParcelID = U.ParcelID

Where U.LandUse IN ('Industrial', 'Commercial')

Group By P.ParcelID

HAVING Count(*) = 2

Assuming that duplicate records never occur in the LandUse table.