category_id, category_pointer, category_name_de, cateogry_name_en
1, 0, 'Hauptebene 1', 'Top Level 1'
2, 0, 'Hauptebene 2', 'Top Level 2'
3, 0, 'Hauptebene 3', 'Top Level 3'
4, 1, 'Unterebene 1 von Hauptebene 1', 'Sub Level 1 from Top Level 1'
5, 1, 'Unterebene 2 von Hauptebene 1', 'Sub Level 2 from Top Level 1'

produkt_id, product_category, product_name_de, product_... 
1, '1#2,3,4,5', 'Testprodukt 1', ...
2, '4#5', 'Testprodukt 2', ...
3, '7#2,3', 'Testprodukt 3', ...
4, '5#2,3', 'Testprodukt 4', ...

Hauptebene 1, Unterebene 1, Produkt 1
NULL, NULL, Produkt 2
NULL, Unterebene 2, Produkt 3
NULL, NULL, Produkt 4
NULL, NULL, Produkt 5
Hauptebene 2, Unterebene 1, Produkt 6
NULL, NULL, Produkt 7

SELECT	cat1.category_id 		AS main_category_id,
		cat2.category_id		AS sub_category_id,
		cat1.category_name_de	AS main_category_name_de,
		cat2.category_pointer,
		cat2.category_name_de 	AS sub_category_name_de,
		product.*,
		LEFT(product.product_category, LOCATE('#', product.product_category)-1) AS product_category_main_id
FROM		category_product 		AS cat1
INNER JOIN	category_product 		AS cat2
ON		cat2.category_pointer = cat1.category_id
INNER JOIN	product
ON		LEFT(product.product_category, LOCATE('#', product.product_category)-1) = cat2.category_id
ORDER BY	main_category_name_de, sub_category_name_de, product_name_de

Hauptebene 1, Unterebene 1, Produkt 1
Hauptebene 1, Unterebene 1, Produkt 2
Hauptebene 1, Unterebene 2, Produkt 3
Hauptebene 1, Unterebene 2, Produkt 4
Hauptebene 1, Unterebene 2, Produkt 5
Hauptebene 2, Unterebene 1, Produkt 6
Hauptebene 2, Unterebene 1, Produkt 7