Selective Group By?

cereal · #1 (**permalink**) 04-21-09, 07:43

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?

Internet Services

Web Development

Digital Marketing

Consumer Tech