Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2011
    Posts
    29

    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:

    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.target2)) as display_target
    concat('$'FORMAT(p.current_total2)) as display_current
    concat('$'FORMAT(p.target p.current_total2)) as display_needed
    FROM 
    (SA_projects p)
    JOIN SA_projects_to_projects_categories pc ON pc.project_id p.id
    WHERE 
    `pc`.`category_idIN (1,2) AND pc.project_id != 
    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.

  2. #2
    Join Date
    Feb 2011
    Posts
    29

    281 hedelf

    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` = 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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •