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

Pagina: 1
Acties:

Onderwerpen

Vraag


Acties:
  • 0 Henk 'm!

  • Rexomnium
  • Registratie: September 2000
  • Laatst online: 10-07 08:50

Rexomnium

Vincam aut moriar

Topicstarter
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:
1
2
3
4
5
6
7
8
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:
1
2
3
4
5
6
7
8
9
10
11
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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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.

We zijn allemaal vaandeldrager in een optocht van gekwetsten.

Beste antwoord (via Rexomnium op 07-05-2020 09:08)


  • IceM
  • Registratie: Juni 2003
  • Laatst online: 19:42
Zoiets zou toch moeten werken? Geen idee waarom je cases oid zou moeten gebruiken

SQL:
1
2
3
4
5
6
7
8
9
10
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 ]

...

Alle reacties


Acties:
  • 0 Henk 'm!

  • RobIII
  • Registratie: December 2001
  • Niet online

RobIII

Admin Devschuur®

^ Romeinse Ⅲ ja!

(overleden)
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 ]

There are only two hard problems in distributed systems: 2. Exactly-once delivery 1. Guaranteed order of messages 2. Exactly-once delivery.

Je eigen tweaker.me redirect

Over mij


Acties:
  • 0 Henk 'm!

  • DJMaze
  • Registratie: Juni 2002
  • Niet online
je kan dit verder uitwerken met cases en dan order by doen van de concat
code:
1
2
3
4
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 ]

Maak je niet druk, dat doet de compressor maar


Acties:
  • 0 Henk 'm!

  • Rexomnium
  • Registratie: September 2000
  • Laatst online: 10-07 08:50

Rexomnium

Vincam aut moriar

Topicstarter
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.
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 ]

We zijn allemaal vaandeldrager in een optocht van gekwetsten.


Acties:
  • 0 Henk 'm!

  • P_de_B
  • Registratie: Juli 2003
  • Niet online
je kunt eenzelfde tabel meerdere malen gebruiken door een alias te gebruiken:


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

Oops! Google Chrome could not find www.rijks%20museum.nl


Acties:
  • Beste antwoord
  • +1 Henk 'm!

  • IceM
  • Registratie: Juni 2003
  • Laatst online: 19:42
Zoiets zou toch moeten werken? Geen idee waarom je cases oid zou moeten gebruiken

SQL:
1
2
3
4
5
6
7
8
9
10
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 ]

...


Acties:
  • 0 Henk 'm!

  • DJMaze
  • Registratie: Juni 2002
  • Niet online
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 ]

Maak je niet druk, dat doet de compressor maar


Acties:
  • 0 Henk 'm!

  • IceM
  • Registratie: Juni 2003
  • Laatst online: 19:42
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:
1
2
3
4
5
6
7
8
9
10
11
12
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

...


Acties:
  • +4 Henk 'm!

  • RobIII
  • Registratie: December 2001
  • Niet online

RobIII

Admin Devschuur®

^ Romeinse Ⅲ ja!

(overleden)
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:
1
2
3
4
5
6
7
8
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 ]

There are only two hard problems in distributed systems: 2. Exactly-once delivery 1. Guaranteed order of messages 2. Exactly-once delivery.

Je eigen tweaker.me redirect

Over mij


Acties:
  • 0 Henk 'm!

  • Sircuri
  • Registratie: Oktober 2001
  • Niet online

Sircuri

Volledig Appelig

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

disclaimer: NIET getest


SQL:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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;

Signature van nature

Pagina: 1