SQL Query help

Databases
1

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.

2

Putting a where condition on a left joined table makes the join an INNER JOIN. Instead of adding the condition to the WHERE, add it to the ON


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 
AND PF.propertyid=2
3

Thanks Guido you are right of course, but I don’t think that type of join condition is supported by Access. I tried your suggestion and got a “JOIN expression not supported” error.

4

I think it is, but Access kind of s*cks (IMO) with its syntax. Try adding some parenthesis:


ON ( (PF.featureid = F.featureid)
AND (PF.propertyid=2) )
5

I commend you on your genius, thank you!

6

Hello again, sorry to bump this thread but I need a little further help which is related to my question last week.

I now need to show a page that lists all potential property types along side a checkbox and to check the the box when the customers requirements match that property type. For example a customer may be looking for a ‘studio’ or an ‘apartment’ in which case these two options should be checked but ‘house’ and ‘bungalow’ unchecked. You get the idea.

The property types table is simple and uncomplicated.

PropertyTypes
typeid PK
propertytype

The customer’s property requirements is stored in a bit of an odd way and my question will really centre on whether I can work with this data structure and just need some clever SQL or whether I have to change the data structure.

PropertyRequirements
id PK
type
locations
beds
minprice
maxprice
keywords

Here the type field contains a comma separated list of propertytype ids For Example: ‘1, 2’ for ‘Studio’ or ‘Apartment’.

So I tried writing an SQL statement with an IN clause on my Join:

SELECT PT.typeid, PT.propertytype, IIF(PR.type is NULL, 0,1) as [bol] FROM propertytypes as PT LEFT JOIN propertyrequirements as PR on ((PT.typeid IN (Cstr(PR.[Type]))) AND (PR.clientid=2))

I found this worked only if there was a single value in the PR.Type field EG: ‘1’ but not if there were multiple values ‘1, 2’

I’m not sure if I can even reliably use the IN clause in a JOIN like this - is there any way of working with this data structure and writing SQL That will get a reliable result or do I need to rework this data structure?

7

you cannot use IN like this, because as far as the sql is concerned, PR.[Type] is a single value

so ‘1’ will only be found if PR.[Type] is ‘1’, but not if it’s ‘1,2’

IN is essentially a shortcut for a series of equality tests combined with ORs

try this instead –

… ON InStr(PR.[Type],PT.typeid) > 0

8

Thanks

One small problem though, there are 11 records in the propertytypes table. If the Property requirements are ‘11, 2’ then this solution matches typeid 1 as a hit, as ‘1’ occurs within ‘11’.

9

Here the type field contains a comma separated list of propertytype ids For Example: ‘1, 2’ for ‘Studio’ or ‘Apartment’.

The solution is to normalize your database

10

I thought so… its a pig to do that though with all the data already in it.

11

you can avoid this by concatenating commas around both the search string and the searched string…

… ON InStr( ‘,’&PR.[Type]&‘,’ , ‘,’&PT.typeid&‘,’ ) > 0

and, of course, it’ll always require a table scan…

12

Its a fudge, but its a good one that will save a load of work, thanks r937.

13

I think I’ve been staring at this for two long because my mind is mush

Trying to run a keyword search on properties with certain characteristics. Table structure (abridged)

Properties
propertyid PK
locationid FK
typeid FK
beds
price

Propertytypes
typeid PK
propertytype

Locations
locationid PK
location

PropertyFeatures
id PK
featureid FK
propertyid FK

Features aka (characteristics)
featureid PK
name

I need to search for properties of particular typeids in particular locationids priced between A and B and with characteristics like “disabled access” and “sea view”


SELECT P.*, PT.propertytype, L.location FROM 
(((properties as P INNER JOIN locations as L on L.locationid=P.locationid) 
INNER JOIN propertytypes as PT ON P.typeid = PT.typeid) 
INNER JOIN propertyfeatures as PF on PF.propertyid= P.propertyid) 
INNER JOIN features as F on PF.featureid=F.featureid

WHERE P.typeid IN (1, 2, 3) 
AND P.locationid IN (22, 9, 50, 6, 42, 35, 47, 46, 34, 1) 
AND P.price BETWEEN 100000 AND 300000

This is getting me nowhere as its already returning multiple instances of the same property matches and its before I’ve added the clause for checking the characteristics against F.name.

Thoughts?

14

to see where the “multiple instances” are coming from, study your FROM clause

you have a many-to-many relationship between properties and features, and your FROM clause retrieves all features for each property which satisfies the WHERE conditions

so you’re getting one row per feature per property

and yet, your SELECT clause does not include any feature columns, so the results that you see are multiple rows of the same property

make sense?

15

Makes perfect sense, I just don’t know to correct my joins to traverse the many-to-many relationship.

16

your joins don’t need correcting :smiley:

17

but even if I add F.[name] to the fields in SELECT clause I get multiple match instances, how can I reduce this to single record matches so I can produce a human friendly list of results?

18

Hold on Rudy, do you think I might get this if I read the chapter of your site point book ‘Implementing a Many-to-many
Relationship:’

You seem to be covering a very similar scenario.

19

no, you’re getting single matches, but multiple rows for each one, corresponding to the multiple features that each one has

if you exclude any F columns from the SELECT clause, you can use DISTINCT to return only the properties

20

Forgot to come back and thank you for your help DISTINCT solved my problem, cheers.