mysql query for sphinx

Question

Not sure how to do this. The query doesn't need to be executed quickly...it's for a sphinx index...but it needs to include all the data for searching.

Here's the table structure:

person
- main_id
- name

person_attritubes
- attribute_id
- name (example: hair, eyes, height)

person_details
- main_id
- attribute_id
- value (example: blond, brown, blue, green, 5'6", 6'5")

How do I get all the person_attritubes.name as columns and the person_details.value as the rows for those columns in one query?

Answer 1

Join into the person_details table multiple times. You'd need to know the attribute ids though.

SELECT ...,pd1.value AS hair, pd2.value AS eyes FROM person p, person_details pd1, person_details pd2 WHERE p.main_id=pd1.main_id AND pd1.attribute_id=[id for hair] AND p.main_id=pd2.main_id AND pd2.attribute_id=[id for eyes] ...etc...

Answer 2

Found another solution.

The is query is for a sphinx index, and so the details can be concatenated into one column for text searching.

SELECT 
person.main_id AS id, name,
GROUP_CONCAT(person_details.value SEPARATOR ' ') AS content 
FROM person
INNER JOIN person_details ON (person.main_id = person_details.main_id)
GROUP BY person_details.main_id