[mySQL] Een join én een self join, maar hoe dan?

[Rexomnium]

Ik heb een tabel met hoofd- en subcategorieën. Elke categorie krijgt een naam, een unieke ID en als hij onder een bestaande categorie valt, krijgt hij een parentID mee. Die parentID verwijst naar een hoofdcategorie met een unieke ID.

code:

catid   |   catname             |   parentid

1       |   Hoofdcategorie 1           |    NULL
2       |   Hoofdcategorie 2           |    NULL
3       |   Hoofdcategorie 3           |    NULL
4       |   Subcategorie 1      |   2
5       |   Subcategorie 2      |   2
6       |   Subcategorie 3      |   1

Uiteindelijk wil een lijst krijgen met kopjes en subkopjes. Zoiets als:
Hoofdcategorie 1
- Ding 1
- Ding 2
- Ding 49
Subcategorie 3
- Ding 4
- Ding 5
Hoofdcategorie 2
Subcategorie 1
- Ding 6

Etc.


Ik heb een andere tabel (dingen) met dingen die onder bovengenoemde categorieën vallen. Met de catid verwijs ik naar de tabel met categorieën om de juiste categorie eraan te verbinden. Volgens mij allemaal vrij eenvoudig.

Nu wil ik graag een hiërarchische lijst printen met alle dingen uit tabel DINGEN. De lijst begint met alle dingen in hoofdcategorie 1, dan subcategorie 3, dan hoofdcategorie 2, dan subcategorieën 1 en 2, etc.

Om tabellen met elkaar te verbinden weet ik dat je een JOIN zou kunnen gebruiken. Met een LEFT JOIN kom ik een heel eind.

code:

SELECT 
    br.description, bc.catname
FROM 
    dingen AS br
LEFT JOIN 
    categories AS bc
ON 
    br.catid=bc.catid
ORDER BY 
    bc.catname
ASC

Met bovenstaande code krijg ik keurig netjes een lijst met alle dingen en de bijbehorende categorie. En dat op alfabetische volgorde. Maar nu wil ik ook de hoofdcategorieën weten, als die er zijn. Dus ik gebruik een extra LEFT JOIN om nogmaals de categorieëntabel bij te voegen, maar ditmaal om de namen van de hoofdcategorieën te verkijgen.

code:

SELECT 
    br.description,
    bcc.catname,
    bcp.catname
FROM 
    dingen AS br
LEFT JOIN
    categories AS bcc
ON
    br.catid = bcc.catid
LEFT JOIN
    categories AS bcp
ON
    bcc.catid = bcp.parentid
ORDER BY 
    bcp.catname 
ASC

Met bovenstaande krijg ik nagenoeg hetzelfde resultaat als de eerste selectquery. Het enige verschil is dat ik een extra kolom krijg waar overal NULL ingevuld staat. Volgens mij volg ik redelijk de stappen die deze meneer in zijn relaas zet: http://mikehillyer.com/ar...erarchical-data-in-mysql/

Kan iemand mij een schop in de goeie richting geven? Heb ik mijn tabellen verkeerd opgebouwd? Wat is er verkeerd aan mijn query? Het zal vast iets eenvoudigs zijn, maar ik heb er nu al flink wat uren ingestopt en ik kom er niet meer uit.

[RobIII]

Rexomnium schreef op donderdag 7 mei 2020 @ 00:41:
Dus ik gebruik een extra LEFT JOIN om nogmaals de categorieëntabel bij te voegen

Uh, nee, je joined dingen, categories en budget_categories...

[Voor 5% gewijzigd door RobIII op 07-05-2020 01:00]

[DJMaze]

je kan dit verder uitwerken met cases en dan order by doen van de concat

code:

select
case when c2.naam is null then naam
from categories
left join categories

Maar goed, het wordt sowieso een zooitje

[Voor 11% gewijzigd door DJMaze op 07-05-2020 01:09]

[Rexomnium]

RobIII schreef op donderdag 7 mei 2020 @ 00:59:
[...]

Uh, nee, je joined dingen, categories en budget_categories...

O, ja, stom.. foutje met overnemen en versimpelen.

Ik wil dingen met categories joinen en vervolgens nog een keer met categories.

DJMaze schreef op donderdag 7 mei 2020 @ 01:05:

Maar goed, het wordt sowieso een zooitje

Ja... en waarom? Zit mijn database verkeerd in elkaar? Hoe had ik dat anders en simpeler kunnen doen?

[Voor 26% gewijzigd door Rexomnium op 07-05-2020 08:35]

[P_de_B]

je kunt eenzelfde tabel meerdere malen gebruiken door een alias te gebruiken:


[..]join categories cat1 on ...
join categories cat2 on

[IceM]

Zoiets zou toch moeten werken? Geen idee waarom je cases oid zou moeten gebruiken

SQL:

SELECT 
    d.description,
    c.catname,
    p.catname AS parent_catname
FROM
    dingen d
JOIN
    categories c ON c.catid = d.catid
LEFT JOIN
    categories p ON p.catid = c.parentid

[Voor 9% gewijzigd door IceM op 07-05-2020 08:48]

[DJMaze]

Rexomnium schreef op donderdag 7 mei 2020 @ 08:32:
Ja... en waarom? Zit mijn database verkeerd in elkaar? Hoe had ik dat anders en simpeler kunnen doen?

Wat als je straks 3-4-5 levels diep gaat?
Hoeveel joins?
Hoe zorg je voor een juiste order by?

IceM schreef op donderdag 7 mei 2020 @ 08:48:
Zoiets zou toch moeten werken? Geen idee waarom je cases oid zou moeten gebruiken

hoe doe jij de "order by" zodat alles netjes staat zoals hij wil?
Want met order by parentid ben je er niet

om die redenen heb ik meestal een extra order/weight kolom met waardes:
001
001|001
001|002
001|002|001
002
etc.

dan heb ik 1 select zonder joins, order by die waarde en klaar.

[Voor 15% gewijzigd door DJMaze op 07-05-2020 13:15]

[IceM]

DJMaze schreef op donderdag 7 mei 2020 @ 13:05:
[...]

hoe doe jij de "order by" zodat alles netjes staat zoals hij wil?
Want met order by parentid ben je er niet

Euh, zoiets? Het klopt dat deze structuur voor multi-level dingen niet echt geschikt is misschien maar daar vraagt hij ook niet om.

SQL:

SELECT 
    d.description,
    c.catname,
    p.catname AS parent_catname
FROM
    dingen d
JOIN
    categories c ON c.catid = d.catid
LEFT JOIN
    categories p ON p.catid = c.parentid
ORDER BY 
    catname ASC, parent_catname ASC

[RobIII]

DJMaze schreef op donderdag 7 mei 2020 @ 13:05:
Wat als je straks 3-4-5 levels diep gaat?
Hoeveel joins?
Hoe zorg je voor een juiste order by?

Wat als je NIET 3-4-5 levels diep gaat?
Wat als je je query gewoon dynamisch genereert?

SQL:

SELECT x, catname, p1.catname AS p1_catname, p2.catname AS p2_catname, 
    p3.catname AS p3_catname, ...
FROM dingen d
JOIN categories c ON c.catid = d.catid
LEFT JOIN categories p1 ON p1.catid = c.parentid
LEFT JOIN categories p2 ON p2.catid = p1.parentid
LEFT JOIN categories p3 ON p3.catid = p2.parentid
...

Werkt prima tientallen niveaus diep.

Maak nou eens niet altijd alles moeilijker dan 't is. Hier heeft TS toch helemaal niet om gevraagd? If anything zaai je alleen maar verwarring; met je eerdere post in dit topic ook. TS heeft moeite om het concept parent/child uitgewerkt te krijgen en dan moet er een case when... bijgehaald worden met een concat omdat...

DJMaze schreef op donderdag 7 mei 2020 @ 13:05:
hoe doe jij de "order by" zodat alles netjes staat zoals hij wil?
Want met order by parentid ben je er niet

om die redenen heb ik meestal een extra order/weight kolom met waardes:
001
001|001
001|002
001|002|001
002
etc.

dan heb ik 1 select zonder joins, order by die waarde en klaar.

En wat nou als TS wil sorteren op bloedgroep en daarbinnen op de stand van de maan? Behalve tijdens paasweekend?

Ook hier weer... tjonge, als TS dat nodig heeft komt 'ie er vanzelf wel om vragen als 'ie er niet uit komt...

Overigens is een <whatever>separated (komma, pipe, snowman) value in je tabellen in 999 v.d. 1000 gevallen een teken van een slecht ontwerp. Just sayin'.

[Voor 45% gewijzigd door RobIII op 07-05-2020 14:40]

[Sircuri]

Kan moet je niet een CTE opbouwen voor dit. Zoiets?

disclaimer: NIET getest

SQL:

WITH RECURSIVE categorie_CTE AS
(
    SELECT
          bc.catname
        , bc.parentId AS parent
        , 1 AS lvl
      FROM categories AS bc
     WHERE parentId IS NULL
     UNION ALL
    SELECT
          bc_u.catname
        , bc_u.parentId
        , lvl + 1
      FROM categories AS bc_u
     INNER JOIN categorie_CTE cte ON cte.catid = bc_u.parentId
)
SELECT
      br.description
    , cte.catname
  FROM dingen as br
 INNER JOIN categorie_CTE cte ON cte.catid = br.catid;