[SQL] JOIN na LEFT JOIN

[Anoniem: 21156]

Hoi,

Ik heb een probleempje met het opstellen van een query. Hij werkt wel in mijn situatie, maar volgens mij is hij mis.

Stel je hebt volgende relaties:

A: a_id (prim. key), a_name, b_id ((weak) for. key)
B: b_id (prim. key), b_name, c_id (for. key)
C: c_id (prim. key), c_name

Nu is het zo dat A.b_id niet noodzakelijk naar een bestaande B.b_id moet wijzen (ik noem zo'n foreign key 'weak', maar dat terzijde), maar B.c_id moet wel naar een bestaande C.c_id wijzen. Dat betekent dus dat tussen A en B een 'LEFT JOIN' geldt (tussen A.b_id en B.b_id) en tussen B en C een gewone 'JOIN' (tussen B.c_id en C.c_id).

Nu wou ik volgende query maken (MySQL):

code:

SELECT a_name, c_name
FROM A
LEFT JOIN B ON A.b_id = B.b_id
INNER JOIN C ON B.c_id = C.c_id
WHERE a_id = 4

Ik wil dus a_name en c_name opvragen, waarvoor geldt:

- is er een verwijzing van A.b_id naar B.b_id, dan is c_name != NULL
- is er geen verwijzing van A.b_id naar B.b_id, dan is c_name == NULL

Bovenstaande query werkt perfect in het eerste geval, maar niet in het tweede; dan geeft hij gewoon geen resultaten terug.

Als ik de 'INNER JOIN' vervang door een 'LEFT JOIN', dan werken beide gevallen. Maar dit is geen oplossing volgens mij, want het werkt gewoon 'toevallig':

omdat in mijn opzet de key B.c_id altijd (!) wijst naar een bestaande C.c_id, zal in dit geval de tweede (de nieuwe) 'LEFT JOIN' eigenlijk een gewone 'JOIN' zijn (want voor elke linkerkant B.c_id is er altijd een rechterkant C.c_id).
Maar wat als ik die restrictie niet had, en er dus toch waarden van B.c_id konden bestaan die niet voorkomen in C.c_id? Dan is het aanpassen van de 'INNER JOIN' naar 'LEFT JOIN' niet meer juist, want dan kan hij in bepaalde gevallen teveel teruggeven (als er voor een linkerkant B.c_id geen rechterkant C.c_id is)?

Mijn vraag is dus: hoe moet ik de query dan wel maken?

[ Voor 8% gewijzigd door Anoniem: 21156 op 31-12-2003 15:37 ]

[Anoniem: 31450]

Dit zou dan wel moeten werken

code:

SELECT A.a_name, C.c_name
FROM A, B, C
LEFT JOIN B ON A.b_id = B.b_id
WHERE A.a_id = 4 AND B.c_id = C.c_id

Doch niet getest

[Gert]

Je wilt C joinen op B, niet op A zoals je nu doet. Wat je dus moet doen is de query herschrijven zodat de join van C op B genest is in de join van B op A. Dit nesten doe je door () op de juiste plek neer te zetten.

code:

SELECT .. FROM A 
LEFT JOIN ( B 
    INNER JOIN C 
    ON B.id = C.id
) ON A.id = B.id

Op deze manier zou hij alles uit A moeten selecteren en alleen die records uit B, en dus C waar A een relatie heeft met B.

[Anoniem: 21156]

Gert schreef op 31 december 2003 @ 16:13:
Je wilt C joinen op B, niet op A zoals je nu doet. Wat je dus moet doen is de query herschrijven zodat de join van C op B genest is in de join van B op A. Dit nesten doe je door () op de juiste plek neer te zetten.

code:

SELECT .. FROM A 
LEFT JOIN ( B 
    INNER JOIN C 
    ON B.id = C.id
) ON A.id = B.id

Op deze manier zou hij alles uit A moeten selecteren en alleen die records uit B, en dus C waar A een relatie heeft met B.

Ik twijfel of dit gaat lukken, maar ik zal het proberen!

Gert schreef op 31 december 2003 @ 16:13:
Je wilt C joinen op B, niet op A zoals je nu doet. Wat je dus moet doen is de query herschrijven zodat de join van C op B genest is in de join van B op A. Dit nesten doe je door () op de juiste plek neer te zetten.

code:

SELECT .. FROM A 
LEFT JOIN ( B 
    INNER JOIN C 
    ON B.id = C.id
) ON A.id = B.id

Op deze manier zou hij alles uit A moeten selecteren en alleen die records uit B, en dus C waar A een relatie heeft met B.

Aha, dat dat expliciet moest, wist ik niet!

Ik heb altijd gedacht dat de volgorde van joins 'automatisch' de nestings impliceert, en dus in mijn voorbeeld de query compiler zelf zou bepalen dat we B en C moeten koppelen, en niet A en C. Op zich vind ik het dom dat je dat zelf moet schrijven, want er valt toch niets te joinen tussen A en C? (ja eigenlijk kan je altijd joinen: product).

Ik hoop dat je oplossing lukt met MySQL

Weet ik ook weer thanks

[ Voor 29% gewijzigd door Anoniem: 21156 op 31-12-2003 17:39 ]

[Anoniem: 21156]

De oplossingen blijken toch niet te werken

code:

SELECT a_name, c_name
FROM A
LEFT JOIN (
B INNER JOIN C ON B.c_id = C.c_id
) ON A.b_id = B.b_id
WHERE A.a_id = 1

Stel volgende test-database:

Tabel A: (a_id, a_name, b_id)
1, Tabel A - 1, 1

Tabel B: (b_id, b_name, c_id)
1, Tabel B - 1, 1
2, Tabel B - 2, 2
3, Tabel B - 3, 3

Tabel C: (c_id, c_name)
1, Tabel C - 1
2, Tabel C - 2

Dan krijg ik:

Kolom A - 1, Tabel C - 1 (juist)
Kolom A - 1, NULL (fout?)
Kolom A - 1, NULL (fout?)

Stel volgende test-database:

Tabel A: (a_id, a_name, b_id)
1, Tabel A - 1, 99

Tabel B: (b_id, b_name, c_id)
1, Tabel B - 1, 1
2, Tabel B - 2, 2
3, Tabel B - 3, 3

Tabel C: (c_id, c_name)
1, Tabel C - 1
2, Tabel C - 2

Dan krijg ik:

Kolom A - 1, NULL (juist)
Kolom A - 1, NULL (juist - maar overbodig)
Kolom A - 1, NULL (juist - maar overbodig)


Het aantal resultaten in de output hangt dus af van de cardinaliteit van de tabel B, maar ik snap niet hoe dat kan? In beide geval zou er juist één resultaat moeten zijn, ipv. 3 (= het aantal rows in B ).

Iemand een idee? Ik staar mij er blind op thanks

_{Ik zou ook graag snappen wat er nu verkeerd loopt, ipv. zomaar ergens een extra conditie of DISTINCT of .. bij te voegen, zonder te snappen waarom het dan wel werkt.}

[ Voor 6% gewijzigd door Anoniem: 21156 op 01-01-2004 19:38 ]

[majornono]

Misschien moet je de query anders aanpakken. Wat je nu doet is bij een record A een record C linken indien er een koppeling bestaat tussen a en c in tabel b. Oftewel, tabel b is een koppeltabel.

Probeer eens of het lukt door een select op tabel b te doen en die dan te joinen met A & C. Ik bedoel zoiets:

SQL:

SELECT *
FROM TabelB
INNER JOIN TabelC
    ON xxx=xxx
LEFT OUTER JOIN TabelA
    ON xxx=xxx
...

offtopic:
Probeer in het vervolg niet letters voor de tabellen te zetten, maar laat de betekenis en inhoud van de tabellen waar het kan intact. Dat werkt vaak duidelijker

[ Voor 20% gewijzigd door majornono op 02-01-2004 17:14 . Reden: joins omgedraaid ]

[EfBe]

Anoniem: 21156 schreef op 31 december 2003 @ 15:37:
Ik heb een probleempje met het opstellen van een query. Hij werkt wel in mijn situatie, maar volgens mij is hij mis.

Stel je hebt volgende relaties:

A: a_id (prim. key), a_name, b_id ((weak) for. key)
B: b_id (prim. key), b_name, c_id (for. key)
C: c_id (prim. key), c_name

Nu is het zo dat A.b_id niet noodzakelijk naar een bestaande B.b_id moet wijzen (ik noem zo'n foreign key 'weak', maar dat terzijde), maar B.c_id moet wel naar een bestaande C.c_id wijzen. Dat betekent dus dat tussen A en B een 'LEFT JOIN' geldt (tussen A.b_id en B.b_id) en tussen B en C een gewone 'JOIN' (tussen B.c_id en C.c_id).

een FK wijst altijd naar een bestaande PK value. Je kunt dit alleen ondervangen door het FK field (A.b_id in dit geval) NULLable te maken en NULL daarin te zetten. Ik denk dat je dit ook bedoeld, klopt dat?

Nu wou ik volgende query maken (MySQL):

code:

SELECT a_name, c_name
FROM A
LEFT JOIN B ON A.b_id = B.b_id
INNER JOIN C ON B.c_id = C.c_id
WHERE a_id = 4

Ik wil dus a_name en c_name opvragen, waarvoor geldt:
- is er een verwijzing van A.b_id naar B.b_id, dan is c_name != NULL
- is er geen verwijzing van A.b_id naar B.b_id, dan is c_name == NULL

Bovenstaande query werkt perfect in het eerste geval, maar niet in het tweede; dan geeft hij gewoon geen resultaten terug.

A LEFT JOIN B levert alle A's op en indien A.b_id != NULL, dan is B ook gevuld, anders NULL. B INNER JOIN C levert alle C's op voor alle B's die zijn gespecificeerd.

Nou, omdat je MySql gebruikt, en ik echt niet weet welke versie, maar het kan dus zijn dat MySql dit een beetje dom optimaliseerd en eerst B INNER JOIN C doet en dan B RIGHT JOIN A.

Werkt dit:

code:

SELECT A.a_name, C.c_name
FROM 
(A LEFT JOIN B ON A.b_id = B.b_id)
INNER JOIN C ON B.c_id = C.c_id
WHERE A.a_id = 4

Zo nee, dan is OF je data niet goed, dus er bestaat gewoon geen C waarvoor dit geldt, OF mySql is te brak voor woorden.

Als ik de 'INNER JOIN' vervang door een 'LEFT JOIN', dan werken beide gevallen. Maar dit is geen oplossing volgens mij, want het werkt gewoon 'toevallig':

Ik vermoed dus dat de volgorde vrolijk door MySql wordt bepaald en jij daar dus niet blij mee bent.

Verder moet je per direct kappen met die naamgeving, want het breekt je in de toekomst alleen maar op. Als je een column van A wilt noemen, zeg je niet a_fieldname, maar A.fieldname. Als er toevallig 2 keer dezelfde naam in een selectlist zitten, dan kun je 1 veelal weglaten (zelfde data) of aliassen. Hou je deze naamgeving (prefixen van columns e.d.) dan leer je dat later steeds moeilijker af.

[ Voor 11% gewijzigd door EfBe op 02-01-2004 18:50 ]

[Anoniem: 21156]

EfBe schreef op 02 januari 2004 @ 18:46:
een FK wijst altijd naar een bestaande PK value. Je kunt dit alleen ondervangen door het FK field (A.b_id in dit geval) NULLable te maken en NULL daarin te zetten. Ik denk dat je dit ook bedoeld, klopt dat?

Dat bedoel ik inderdaad!

A LEFT JOIN B levert alle A's op en indien A.b_id != NULL, dan is B ook gevuld, anders NULL. B INNER JOIN C levert alle C's op voor alle B's die zijn gespecificeerd.

Nou, omdat je MySql gebruikt, en ik echt niet weet welke versie, maar het kan dus zijn dat MySql dit een beetje dom optimaliseerd en eerst B INNER JOIN C doet en dan B RIGHT JOIN A.

Werkt dit:

code:

SELECT A.a_name, C.c_name
FROM 
(A LEFT JOIN B ON A.b_id = B.b_id)
INNER JOIN C ON B.c_id = C.c_id
WHERE A.a_id = 4

Zo nee, dan is OF je data niet goed, dus er bestaat gewoon geen C waarvoor dit geldt, OF mySql is te brak voor woorden.

Dit werkt niet. Het werkt wel als A.b_id 'bestaat' voor B.b_id in B, maar niet in het andere geval (dus als C.c_name in de output NULL zou geweest zijn), dan geeft hij gewoon geen resultaat (zou (4, NULL) moeten zijn). Mijn data is juist veronderstel ik (zie vorige post), dus ik vrees dat het aan MySQL ligt? leuk is dat

Verder moet je per direct kappen met die naamgeving, want het breekt je in de toekomst alleen maar op. Als je een column van A wilt noemen, zeg je niet a_fieldname, maar A.fieldname. Als er toevallig 2 keer dezelfde naam in een selectlist zitten, dan kun je 1 veelal weglaten (zelfde data) of aliassen. Hou je deze naamgeving (prefixen van columns e.d.) dan leer je dat later steeds moeilijker af.

Normaal gezien doe ik dat niet (meer), maar deze keer wel, omdat mij de voorbeelden dan duidelijker leken. Verder snap ik niet echt wat er verkeerd mee is? Het is toch maar een naamgeving, die in principe persoonlijk is? Akkoord, als je in team werkt, moet iedereen ermee kunnen leven, maar wat maakt het uit voor persoonlijk gebruik?

Bedankt voor je hulp!

majornono schreef op 02 januari 2004 @ 17:12:
Misschien moet je de query anders aanpakken. Wat je nu doet is bij een record A een record C linken indien er een koppeling bestaat tussen a en c in tabel b. Oftewel, tabel b is een koppeltabel.

Probeer eens of het lukt door een select op tabel b te doen en die dan te joinen met A & C. Ik bedoel zoiets:

SQL:

SELECT *
FROM TabelB
INNER JOIN TabelC
    ON xxx=xxx
LEFT OUTER JOIN TabelA
    ON xxx=xxx
...

offtopic:
Probeer in het vervolg niet letters voor de tabellen te zetten, maar laat de betekenis en inhoud van de tabellen waar het kan intact. Dat werkt vaak duidelijker

toon volledige bericht

Ik zal dat proberen, kan misschien wel werken, maar zo'n oplossing vind ik wel jammer

_{PS ik heb in mijn query voorbeelden A.a_id = 4 geschreven, terwijl in mijn data A.a_id gelijk aan 1 is; is een foutje, maar ik test het hier telkens met A.a_id = 1, dus er is niets mis met de data en/of query}

[ Voor 23% gewijzigd door Anoniem: 21156 op 04-01-2004 20:50 ]