var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Query Help: m:m join, optimise?
Not even sure how to ask this one properly! I have a working solution but wonder if there's a better way.
I have a familiar m:m table of id's like:
I need to get all projects (& full row from an unshown project table) where a project shares a category_id for a given project_id.
So for project_id = 1
id like to see project_id's 2,3 full rows from project table.
So far I've come up with:
This works but to get here I've had to:
SELECT p . id , p . name , p . excerpt , p . target , p . current_total , concat ( '$' , FORMAT ( p . target , 2 )) as display_target , concat ( '$' , FORMAT ( p . current_total , 2 )) as display_current , concat ( '$' , FORMAT ( p . target - p . current_total , 2 )) as display_needed FROM ( SA_projects p ) JOIN SA_projects_to_projects_categories pc ON pc . project_id = p . id WHERE ` pc `.` category_id ` IN ( 1 , 2 ) AND pc . project_id != 1
1) get the project
2) get all the categories this project is in
3) server side process 2) into a flat array, then csv that to give me the IN() portion
1 & 2 I need to do anyway.
I had a thought to minmise 2) and 3)
So when I get the project I can also get a csv of it's categories at the same time.
SELECT p .* , date_format ( p . created_date , "%d %M %Y" ) as readable_date , date_format ( p . created_date , "%H:%i" ) as readable_time , GROUP_CONCAT ( ppc . category_id ) as category_list FROM ( SA_projects p ) JOIN SA_projects_to_projects_categories ppc ON ppc . project_id = p . id WHERE ` p `.` id ` = 1 AND p . visible = "yes" AND p . state = "active"