[thelist] mysql select brain teaser

[Jeremy Weiss]

Okay, this one is a bit out there for my peanut brain so I thought I'd float it past all you experts out there. This is all in a MySQL 5 database and the search pages I'm building are in PHP 5. I'm working on a real estate site. The property information that I'm wanting to query is split across four tables.

Listings: main table with mls #, beds, baths, and 80+ other fields including an odd field called features.
	--features contains a pipe delimited list of forign keys from the features table.
Features: table has 3 col. id, feature description, and feature category.
Feature_Categories: table has 3 col. CatID, ClassID, and description.
Photos: table with 4 cols. mls_number (fk from Listings), photo_number (as one property can have multiple photos), file_name, last_activity.
To see this visually, go to http://tinyurl.com/5q58dz for a pretty little gif file.


What I'm thinking is one query to select the primary information on left join on photos to get those too (nothing unusual here)
Then I'm thinking of taking that crazy listings.features field, turning it into an array and looping through it to build a SELECT statement like SELECT * FROM features AS f INNER JOIN feature_categories AS fc ON f.feature_category = fc.catid  WHERE xxx or xxx or xxxx or xxx GROUP BY fc.catid. And then just loop through the recordset. 

And I'm reasonably sure this will work. But I don't like it. What I'd like to do is just run a single query to get it all. But I haven't a clue if it can be done. If it can't be done with this structure, I may see if I can massage the data a bit during import and possibly break the listings.features field from the main table and build it in it's own table. But then I'd have 5 tables to join.

Anyway, just looking for some guidence on this one as to what course would be the best.

thanks,
-jeremy