Results 1 to 1 of 1
  1. #1
    Join Date
    Mar 2009
    Posts
    8

    Question Unanswered: Selective Group By?

    i have the following 2 querrys

    Code:
    select
      b.id as block, group_concat(if ( i.block_id_1 = b.id, i.block_id_2, i.block_id_1)) as top, group_concat(d.status) as stat, group_concat(d.owner) as owner
    FROM block AS b
      LEFT JOIN interaction AS i
        ON i.block_id_1 = b.id OR i.block_id_2 = b.id
      LEFT JOIN interaction_data AS d
        ON d.interaction_id = i.id
    group by b.id
    and

    Code:
    select
      n2.id as id,
      n2.name as name,
      (COUNT(n1.id)-1) as depth,
      if( (n2.lft+1) = n2.rgt, 0, 1) as haskids,
      GROUP_CONCAT(distinct n1.id order by n1.lft SEPARATOR '-') as pht
    FROM block AS n1
      INNER JOIN block n2
        ON n2.lft BETWEEN n1.lft AND n1.rgt
    WHERE
      n2.lft BETWEEN (SELECT lft FROM block WHERE id = 1) AND (SELECT rgt FROM block WHERE id = 1)
      AND n2.build_id <= 1000 OR n2.build_id = 1000
    GROUP BY n2.id
    ORDER BY n2.lft
    these work both perfect, but i need to combine these 2 querys into one big querry, i came up with the folowing result

    Code:
    select
      n2.id as id,
      n2.name as name,
      (COUNT(n1.id)-1) as depth,
      if( (n2.lft+1) = n2.rgt, 0, 1) as haskids,
      GROUP_CONCAT(distinct n1.id order by n1.lft SEPARATOR '-') as pht,
      GROUP_CONCAT(d.status),
      GROUP_CONCAT(d.owner),
      GROUP_CONCAT(if ( i.block_id_1 = n2.id, i.block_id_2, i.block_id_1)) as top
    FROM block AS n1
      INNER JOIN block n2
        ON n2.lft BETWEEN n1.lft AND n1.rgt
      LEFT JOIN interaction AS i
        ON n2.id = i.block_id_1 OR n2.id = i.block_id_2
      LEFT JOIN interaction_data AS d
        ON i.id = d.interaction_id
    WHERE
      n2.lft BETWEEN (SELECT lft FROM block WHERE id = 1) AND (SELECT rgt FROM block WHERE id = 1)
      AND n2.build_id <= 1000 OR n2.build_id = 1000
    GROUP BY n2.id
    ORDER BY n2.lft
    now the last query returns the information in a group_concat field n times, i think i know why but i don't have a solution for this.

    This is what i think that the problem is:

    the inner join on block returns x rows
    the 2 left joins return y rows together

    now it seems that the main group_concat return x*y=n rows, is there a posibility to let the group_concat only concat the results from the 2 left joins and not use the results of the inner join?

    How do i do this? or is this the wrong approach for this problem?
    Last edited by cereal; 04-21-09 at 08:47. Reason: Fixed typo

Posting Permissions

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