"Fuzzy" search on keys with relevance

[parrod]

I got three tables; house, attributes, houseattributes:

house table:

house_id
house_location

attributes table:

attrib_id
attrib_title

houseattributes:

ha_id
ha_house
ha_attrib

The idea is that I will fill up the attributes table with stuff like: white-colored, three story, two story, has cellar, has balcony, has garden and so on. I could then populate houseattributes with the house and attribute table-keys (attrib_id, house_id). So if I want to search for a house with a specific attribute, I would do:


Code:
SELECT * FROM house LEFT JOIN houseattributes ON houseattributes.ha_house.house.house_id WHERE ha_attrib = '3'

In this example, "3" is the attribute, as set in the attributes table).

Now for the problem.

I want the ability list all houses that has several attributes se -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --

[gathy]

Don't use things like:
"has basement" just use "basement".

also in the houseattributes table you don't need ha_id. you don't want to look the attribute up by having to know that roof is ha_id 3.

Lets insert some test data:

Code:
house table:
1, '10 primrose'
2, '8 primrose'
3, '2 primrose'

attributes table:
1, 'roof'
2, 'double garage'
3, 'windows'
4, 'door'
5, 'deck'

house_attributes:
1, 1
1, 3,
1, 4
1, 5
2, 1
2, 2
2, 3
2, 4
3, 1
3, 3
3, 5


So now you want to know which houses have a roof and a deck, here is the basic search we would do for that:


Code:
select
house_id,
house_location,
count(*) as attributes
from house
left outer join
house_attributes
-- This message may have been cut off and the rest will only be shown to members. To become a member, click here --