Hi Guys I need a bit of help with some SQL.
I have an Access database table called features, it contains a list of all the possible features a property might have, views, furnishing, parking spaces, garden etc. It has the following structure
Features
featureid PK
name
I have another table, PropertyFeatures, this table contains a list of the actual features a property has. It has the following structure:
PropertyFeatures
id PK
featureid FK
propertyid FK
Now on my property editor interface I have a list of all possible features with checkboxes and I tick the checkbox if the property has this feature.
I need to write a query that will give me an output of the featureid, name and a boolean value as to whether the property in question has this feature.
So far I have:
SELECT F.featureid, F.[name], IIF(PF.Featureid is null, 0,1) as [bol] FROM
features as F LEFT JOIN propertyfeatures as PF on PF.featureid =F.featureid
But obviously this doesn’t narrow it down to a treatment of an individual property’s feature. So I added WHERE propertyid=2
SELECT F.featureid, F.[name], IIF(PF.Featureid is null, 0,1) as [bol] FROM
features as F LEFT JOIN propertyfeatures as PF on PF.featureid =F.featureid WHERE propertyid=2
But this then only returns the property features that have actually been selected by this property and does not return the unselected features with the boolean value 0. Bit stuck on where to go from here.
Hopefully I’ve explained myself clearly enough.
Can anyone help?
Thanks in advance.