[SQL] Meerdere tabellen joinen met zelfde id

[Rixard]

Ik ben vandaag al een tijdje aan het stoeien met een query waar ik niet uit kom.

Mijn (vereenvoudigde) tabellen:
teams:

id_team

naam

id_trainer1

id_trainer2

relaties:

id_relatie

naam

Wat wil ik bereiken:
Ik wil de naam van trainer1 en trainer2 ophalen. De namen van deze trainers zitten in de tabel 'relaties' en worden gelinkt via hun id (id_relatie -> id_trainer1, maar ook id_trainer2)

Wat heb ik geprobeerd:
Het lukt mij om de gegevens van 1 trainer op te vragen met deze query:

SELECT teams.naam,
relaties.naam AS trainernaam,
FROM teams
LEFT JOIN relaties ON teams.id_trainer1 = relaties.id_relatie
ORDER BY teams.naam


Wat ik heb geprobeerd (maar niet werkt) is bijvoorbeeld:
SELECT teams.naam,
relaties.naam AS trainer1naam,
relaties.naam AS trainer2naam,
FROM teams
LEFT JOIN relaties ON teams.trainer1 = relaties.id_relatie
LEFT JOIN relaties ON teams.trainer2 = relaties.id_relatie
ORDER BY teams.naam

Nu is mijn vraag:
Hoe kan ik uit meerdere tabellen informatie halen op basis van dezelfde key, met verschillende waarden?

[PvdE]

Hiervoor kan je volgens mij een 'alias' aanmaken. Even snel uit de losse pols, het volgende voorbeeld zou in ieder geval moeten werken op MSSQL.

code:

SELECT   teams.naam,
        r1.naam AS trainer1naam,
        r2.naam AS trainer2naam
FROM    teams
        LEFT JOIN relaties AS r1 ON teams.trainer1 = r1.id_releatie
        LEFT JOIN relaties AS r2 ON teams.trainer2 = r2.id_releatie
ORDER BY teams.naam

dit geeft je bij de volgende data:

code:

id_relatie naam
------------------------
1           a         
2           b         
3           c         
4           d

code:

id_team     naam       trainer1    trainer2
------------------------------------------------------------
1           team a     1           NULL
2           team  b    NULL        2
3           team ab    1           2
4           team c     3           NULL
5           team ac    1           3

het volgende resultaat

code:

naam       trainer1naam trainer2naam
------------------------------------------------------------
team  b    NULL         b         
team a     a            NULL
team ab    a            b         
team ac    a            c         
team c     c            NULL

[Janoz]

Maar de echte oorzaak van je probleem blijft natuurlijk dat je database ontwerp niet goed is. Nummertjes in de kolomnamen is sowieso een erg sterke indicatie van het gebrek aan normaliseren.

De werkelijke oplossing van je probleem is het verwijderen van de twee trainer kolommen en het toevoegen van een koppeltabel.

[!null]

Het is wel mogelijk een query te schrijven die doet wat je wil, maar zoals Janoz zegt, het database ontwerp is eigenlijk niet goed.

[Rixard]

De nummers is de kolomnamen zijn omdat er een hoofdtrainer (en daarvan is er altijd maar 1) en een assistent-trainer (ook altijd maar 1) is.

Het is dus niet zo dat ze dezelfde functie bekleden. Misschien had ik in het voorbeeld beter 'trainer' en 'verzorger' kunnen gebruiken.

Er had dus ook kunnen staan:

id_team

id_trainer

id_verzorger

Of is het verstandiger om iets heel anders te doen?

[Janoz]

Op zich je je structuur in dat geval beter te verdedigen en is de enige echte fout nog je naamgeving ,
maar zijn er altijd maar 1 hoofd trainer en 1 assistent? Kunnen er niet meer assistenten of verzorgers zijn?

Een andere mogelijkheid zou kunnen zijn om niet een simpele koppeltabel te gebruiken, maar een koppeltabel waar als extra kolom een rol opgenomen is.

[Rixard]

Bedankt voor je input

Je hebt helemaal gelijk dat het niet ondenkbaar is dat er meerdere hoofd- en/of assistent-trainers kunnen zijn.

Als ik het goed begrijp bedoel je dan zoiets:

relaties (met persoonsgegevens)

id_relatie

naam

teams

id_team

naam

teams_relaties

id_relatie

id_team

id_functie

functies

id_functie

naam

[ Voor 8% gewijzigd door Rixard op 17-09-2009 14:09 ]

[!null]

Ja dit kan. Al is het sinds je tweede uitleg een beetje subjectief geworden wat beter is.
Met dit ontwerp kan je iig wel alle kanten op, het meest flexibel.