Query Help: m:m join, optimise?

RGM · #1 (**permalink**) 02-28-11, 18:18

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:

project_id, category_id
1,1
1,2
2,1
3,2
4,3

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:

PHP Code:


			
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

This works but to get here I've had to:
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.

RGM · #2 (**permalink**) 02-28-11, 18:35

I had a thought to minmise 2) and 3)

with:

PHP Code:


			
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"

So when I get the project I can also get a csv of it's categories at the same time.

Internet Services

Web Development

Digital Marketing

Consumer Tech