Mysql query for sphinx
I don’t know how to do it. The query doesn't need to be done quickly ... it's for a sphinx index ... but it should include all the data to search for.
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 person_attritubes.name as columns and person_details.value as rows for those columns in one query?
0
a source to share
2 answers
Found another solution.
The is query is for a sphinx index, and so the details can be combined into a single column to search for text.
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
0
a source to share
Enter the person_details table multiple times. You need to know the IDs of the attributes, 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...
0
a source to share