How to form a SQL query for a one to many relation?

I have two MySQL tables: house and features. This is for a real estate website.

house has this structure:

id | address | ... some other fields

features has this structure:

id | house_id | feature | value

as you can guess, a house can have multiple features, so a 1:n relationship exists.

Now, I'm implementing a search form for houses. I need to filter the results based on the features selected by the user.

Sample scenario: get all houses which have the features garage and garden, and a list of features in those houses.

What would be the bes开发者_开发技巧t query to suit my needs? I'm not familiar with JOIN operations, so I need your help!

(OPTIONAL) If you can provide a Yii Framework-specific solution, it would be awesome :)

SELECT * 
FROM HOUSE H JOIN FEATURES F ON H.HOUSE_ID = F.HOUSE_ID
WHERE FEATURE IN ('GARDEN','GARAGE');

If you need only the columns from the House table:

SELECT * from HOUSE where ID in (SELECT ID from FEATURES where FEATURE IN('GARDEN','GARAGE'));

If you need the entire details:

SELECT * FROM HOUSE H JOIN FEATURES F ON H.HOUSE_ID = F.HOUSE_ID WHERE FEATURE IN ('GARDEN','GARAGE');

You don't need to join it manually if you are using Yii.

It's easier if you use MySQL (over SQLite) and if you use InnoDB tables (over MyISAM tables).

You need to create a foreign key from Features (house_id column) to Houses (house_id column)

If you build your model using Gii, then it will create the "relation" function for you automatically.

Then in Yii, you would call House->features to get an array of feature rows.

You can pass a CDbCriteria object in the Fill() call to filter the houses that the user selected.