Take the 2-minute tour ×

I am trying to figure out a way to query a property feature lookup table.

I have a property table that contains rental property information (address, rent, deposit, # of bedrooms, etc.) along with another table (Property_Feature) that represents the features of this property (pool, air conditioning, laundry on-site, etc.). The features themselves are defined in yet another table labeled Feature.

    pid - primary key
    other property details

    fid - primary key

    id - primary key
    pid - foreign key (Property)
    fid - foreign key (Feature)

Let say someone wants to search for property that has air conditioning, and a pool and laundry on-site. How do you query the Property_Feature table for multiple features for the same property if each row only represents one feature? What would the SQL query look like? Is this possible? Is there a better solution?

Thanks for the help and insight.

share|improve this question
You can actually drop the "id" column on Property_Feature table and make pid and fid a combined primary key. –  Mats Fredriksson Jun 18 '09 at 16:32
add comment

3 Answers

up vote 1 down vote accepted

In terms of database design, yours is the right way to do it. It's correctly normalized.

For the query, I would simply use exists, like this:

select * from Property
exists (select * from Property_Feature where pid = property.pid and fid = 'key_air_conditioning')
exists (select * from Property_Feature where pid = property.pid and fid = 'key_pool')

Where key_air_conditioning and key_pool are obviously the keys for those features.

The performance will be OK even for large databases.

share|improve this answer
This is fairly unfriendly, isn't it? Are you expecting him to memorize keys? Why not use joins so you can just name the feature instead of trying to remember what could potentially be an identity column? –  Eric Jun 18 '09 at 16:30
Eric, because he's doing it in the context of a program obviously, and the key is probably near hand. I prefer the EXISTS because are cleaner and easier to build, specially if the number of conditions is variable. –  tekBlues Jun 18 '09 at 16:32
Joins do tend to get a bit messy with variable numbers of conditions...I'll have to think about how to do that cleaner...and as a challenge, I want to do it a bit differently than you have :) –  Eric Jun 18 '09 at 16:45
Thanks for the help. I was stumped. –  stillLearning Jun 18 '09 at 19:17
add comment

Here's the query that will find all the properties with a pool:

    property p
    inner join property_feature pf on
        p.pid = pf.pid
    inner join feature f on
        pf.fid = f.fid
    f.name = 'Pool'

I use inner joins instead of EXISTS since it tends to be a bit faster.

share|improve this answer
Thanks Eric I completely overlooked the EXISTS keyword. –  stillLearning Jun 18 '09 at 19:18
add comment

You can also do something like this:

    FROM Property p
   WHERE 3 =
         ( SELECT COUNT(*)
             FROM Property_Feature pf
                , Feature f
            WHERE pf.pid = p.pid
              AND pf.fid = f.fid
              AND f.name in ('air conditioning', 'pool', 'laundry on-site')

Obviously, if your front end is capturing the fids of the feature items when the user is selecting them, you can dispense with the join to Feature and constrain directly on fid. Your front end would know what the count of features selected was, so determining the value for "3" above is trivial.

Compare it, performance wise, to the tekBlues construction above; depending on your data distribution, either one of these might be the faster query.

share|improve this answer
I appreciate the feedback. All of these solutions I will try. –  stillLearning Jun 18 '09 at 19:18
add comment

Your Answer

