distinct ? group by ?

[anatak]

Hello

I have a query that produces this output

 

cat id cat subcatid subcat elementid element

1 Event 1 Chat party 3 Dinner party

1 Event 1 Chat party 4 Nomikai

1 Event 2 Class   5 Cooking class

1 Event 2 Class   6 Salsa class

 

I would like to get the output like this

 

cat id cat subcatid subcat elementid element

1 Event 1 Chat party   3 Dinner party

          4 Nomikai

  2   Class 5     Cooking class

            6 Salsa class

 

here is the query

SELECT content_cat_m2m_sub_cat.CategoryId as CatId, content_category.Lan_1 as CatLan_1, content_cat_m2m_sub_cat.SubCategoryId as SubCatId, content_sub_category.Lan_1 as SubCatLan_1, content_sub_cat_m2m_element.ElementId, content_element.Lan_1 as Element_1 FROM content_cat_m2m_sub_cat, content_sub_cat_m2m_element LEFT JOIN content_category ON content_cat_m2m_sub_cat.CategoryId = content_category.Id LEFT JOIN content_sub_category ON content_cat_m2m_sub_cat.SubCategoryId = content_sub_category.Id LEFT JOIN content_element ON content_sub_cat_m2m_element.ElementId = content_element.Id WHERE content_cat_m2m_sub_cat.SubCategoryId = content_sub_cat_m2m_element.SubCategoryId ORDER BY CatLan_1, SubCatLan_1, Element_1 ASC ;

 

Can anyone point me in the right direction ?

 

kind regards

anatak

[Raghav]

why would you want to do that?

The output produced by your query has sufficient info to process the result with php.

[anatak]

Hey Raghav,

 

I tried to manipulate this with php but I can't get it to work.

could you help me to write the code that only displays one time the catId and cat ?

 

thanks

anatak

[fenway]

I don't understand what output you're trying to obtain.

[anatak]

at this moment in every row is the category information. (EVENT)

I would like to output the category information only 1 time instead of repeating it in every row / line

the same with the subcategories (CHAT PARTY and CLASS)

I would like to output only one time every subcategory.

 

[btherl]

Can you post your current php code?  Alternatively you can try to add something like this yourself:

 

$last_subcatid = null;
while ($row = mysql_fetch_assoc($result)) {
  if ($row['subcatid'] !== $last_subcatid) {
    # New subcatid.  We must print out the full data.
  } else {
    # Still the same subcatid.  Print out only the element
  }
}

[anatak]

Hello btherl

 

Thanks for the hint

I could get it to work.

here is the code in case someone is interested in this

foreach($Result04 AS $row04){
	//check if it is a new category
	if($cat!=$row04['CatId']){
		//new category					
		$cat=$row04['CatId'];
		echo'<tr><td>'.$row04['CatId'].'</td><td>'.$row04['CatLan_'.$default_language].'</td>';
		//check if it is a new subcategory
		if($subcat!=$row04['SubCatId']){
			//new subcategory
			$subcat=$row04['SubCatId'];				
			echo'<td>'.$row04['SubCatId'].'</td><td>'.$row04['SubCatLan_'.$default_language].'</td>';
		}else{
			//not new subcategory
			echo'<td></td><td></td>';
		}
		echo '<td>'.$row04['ElementId'].'</td><td>'.$row04['Element_'.$default_language].'</td></tr>';
	}else{
		//not new category
		echo'<tr><td></td><td></td>';
		//check if it is a new subcategory
		if($subcat!=$row04['SubCatId']){
			//new subcategory
			$subcat=$row04['SubCatId'];				
			echo'<td>'.$row04['SubCatId'].'</td><td>'.$row04['SubCatLan_'.$default_language].'</td>';

		}else{
			//not new subcategory
			echo'<td></td><td></td>';
		}
		echo '<td>'.$row04['ElementId'].'</td><td>'.$row04['Element_'.$default_language].'</td></tr>';
	}

}

[anatak]

Now I have a real SQL question

With the query I have now I only select the categories that are in relation with a subcategory.

How can I select all the categories ? even if they are not linked in the content_cat_m2m_sub_cat table ?

 

I guess it is something with the joins.

I tried to look up the join syntax of mysql but I can't find the answer I am looking for.

 

What join selects all the rows even if the join condition is not met ?

 

i hope you understand my question

 

SELECT content_cat_m2m_sub_cat.CategoryId AS CatId, content_category.Lan_1 AS CatLan_1, content_cat_m2m_sub_cat.SubCategoryId AS SubCatId, content_sub_category.Lan_1 AS SubCatLan_1, content_sub_cat_m2m_element.ElementId, content_element.Lan_1 AS Element_1
FROM content_cat_m2m_sub_cat, content_sub_cat_m2m_element
LEFT JOIN content_category ON content_cat_m2m_sub_cat.CategoryId = content_category.Id
LEFT JOIN content_sub_category ON content_cat_m2m_sub_cat.SubCategoryId = content_sub_category.Id
LEFT JOIN content_element ON content_sub_cat_m2m_element.ElementId = content_element.Id
WHERE content_cat_m2m_sub_cat.SubCategoryId = content_sub_cat_m2m_element.SubCategoryId
ORDER BY CatLan_1, SubCatLan_1, Element_1 ASC  LIMIT 0 , 30

[bubblegum.anarchy]

LEFT JOIN