Inner Join Tables and Order By
Posted on 2007-08-10
I have three mysql tables as below.
field_1_id can have many field_3_id's
I would like to select field_1_text and order by the total of all the field_3_number's for each field_1_id. I would also like to return this total.
I am only interested in returning one row for each field_1_id.
field_1_id = field_2_1_id
field_2_3_id = field_3_id
What I have at the moment is (it doesn't work because it has pseudo in it):
SELECT field_1_text, sum(field_3_number for each field_1_id) as total_number
INNER JOIN table_2 field_1_id = field_2_1_id
INNER JOIN table_3 field_3_id = field_2_3_id
ORDER BY sum(field_3_number for each field_1_id) ASC