Return Main Table if 2 values exists in sub table

[thankgodfortektips]

hey Guys,

I have a table called Property and one called features. One property can have many features. I need a query that will return only the properties that have both featureID = to 1 and 3.

After searching I have seen the IN command but this will return a record if it has 1 or 3. I need it to only return Property records that have Features records = 1 and 3 (so 2 rows in the features table where FeatureID = 1 and 3).

I hope this makes sense.

Thanks in advance!

 

[JBinQLD]

G'day,

I imagine thus:

table [property]
Property_ID Autonumber PK
Property_Address Text
etc


table [features]
feature_ID Autonumber PK
Property_ID Number FK
Feature Text

That being the case, wouldn't this work?:

SELECT DISTINCT Property.PropertyName
FROM features INNER JOIN Property ON features.property_ID = Property.Property_ID
WHERE (((features.feature_ID)=1 Or (features.feature_ID)=2));

Good luck!

JB

 

[PHV]

SELECT P.*
FROM (Property P
INNER JOIN features F1 ON P.PropertyID = F1.PropertyID)
INNER JOIN features F3 ON P.PropertyID = F3.PropertyID
WHERE F1.FeatureID=1 AND F3.FeatureID=3

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JBinQLD]

Colour me embarrassed! Sorry for the bum steer, of course i meant AND rather than OR and 3 rather than 2. Oooops. Sorry mate, hope i didn't waste any of your time

JB

 

[PHV]

JBinQLD, you really meant this ?
WHERE (((features.feature_ID)=1 And (features.feature_ID)=3))