[MySQL] Query zonder subquery

[Priet]

Mijn database ziet er als volgt uit:

Er kunnen wat foutjes inzitten, het is nog volop in ontwikkeling.

Toelichting:
De database is voor een basisschool. Elk jaar is er een andere samenstelling van groepen. De groepen voor een schooljaar worden bijgehouden in de koppeltabel koppel_jaargroepen. In de andere koppeltabel koppel_jaargroepengebruikers wordt bijgehouden welke gebruiker (docent) dan bij zo'n combinatie van schooljaar en groep hoort.

De database wordt op een website gebruikt. Op een van die pagina's van die website wil ik uit de database het volgende ophalen:
Alle groepen voor een schooljaar die voor een gebruiker nog niet zijn opgenomen in de koppeltabel koppel_jaargroepengebruikers

Om alle groepen op te halen die al wél zijn opgenomen gebruik ik de volgende query:

code:

SELECT   groepen.naam 
FROM       koppel_jaargroepengebruikers, koppel_jaargroepen, groepen 
WHERE    koppel_jaargroepengebruikers.gebruiker_id = '$gebruiker_id'
    AND koppel_jaargroepengebruikers.jaargroep_id = koppel_jaargroepen.id
    AND koppel_jaargroepen.schooljaar_naam = '$schooljaar'
    AND koppel_jaargroepen.groep_id = groepen.id

Om het wel te veranderen in niet en zodoende aan mijn eigen vraag te voldoen zou ik gebruik kunnen maken van het volgende:

code:

SELECT   groepen.naam 
FROM       groepen
WHERE NOT IN
       (SELECT   groepen.naam 
       FROM    koppel_jaargroepengebruikers, koppel_jaargroepen, groepen 
       WHERE     koppel_jaargroepengebruikers.gebruiker_id = '$gebruiker_id'
           AND  koppel_jaargroepengebruikers.jaargroep_id = koppel_jaargroepen.id
           AND  koppel_jaargroepen.schooljaar_naam = '$schooljaar'
           AND  koppel_jaargroepen.groep_id = groepen.id)

Nu worden alle groepen getoond die voor een gebruiker nog niet zijn gekoppeld.

Het probleem is dat ik deze subquery niet kan gebruiken omdat dat MySQL 4.0 dit nog niet ondersteund. 4.1 wel, maar dat wordt (nog) niet gebruikt door de hostingprovider. Ik zal dus de query moeten herschrijven zodat er geen subquery meer gebruikt wordt.

De manier om dat te doen is mbv JOIN. Helaas kom ik daar niet helemaal uit. Ik dacht: laat ik het stap voor stap aanpakken en dan de eerste query (de wel-query) herschrijven en dan later die veranderen zodat die voldoet aan wat ik wil bereiken.
Uiteindelijk heb ik er dit van gebakken:

code:

SELECT   groepen.naam
FROM    koppel_jaargroepengebruikers
    INNER JOIN koppel_jaargroepen ON ( jaargroep_id = koppel_jaargroepen.id ) 
    INNER JOIN groepen ON ( groep_id = groepen.id )
WHERE    schooljaar_naam = '$schooljaar'
    AND  gebruiker_id = '$gebruiker_id'

En dat werkt

Maar bij de volgende stap lukt het mij niet meer. Hoe moet ik hier de niet-query van maken mbv JOIN? Ik heb van alles geprobeerd met FULL OUTER JOIN en LEFT OUTER JOIN etc. maar wat eigenlijk echt goed lukt is het genereren van syntax errors of de meest vreemde resultaten

Hoe schrijf ik nu de subquery om? Een eenvoudige subquery lukt mij nog wel, maar dit gaat mij even iets boven m'n pet

[ Voor 7% gewijzigd door Priet op 04-04-2004 12:27 . Reden: Jointjes eruit gehaald :p ]

[Alex]

Je database indeling is dus eigenlijk net niet gemaakt voor deze actie . Kijk eens naar LEFT JOIN, dat is een INNER JOIN maar dan ondergeschikt, als de join geen resultaten krijgt, krijg je toch van de standaard tabel resultaten.

[ripexx]

code:

SELECT DISTINCTROW l.lid_id, l.voornaam, l.achternaam, l.tussenvoegsel
FROM leden AS l, team_lid
  LEFT JOIN team
    ON team_lid.team_id = team.team_id
WHERE team_lid.lid_id <> l.lid_id
  AND team.seizoen_id = ".$sid."
  AND l.status = 'S';

Oke deze code/query is niet netjes en zeer traag maar ik heb op dit moment geen tijd er voor. Aangezien deze maar door enkele kan worden aangroepen en niet vaak gebruikt zal worden is het niet zo'n probleem.

Deze query geeft de niet ingedeelde leden in een bepaald seizoen.

Ik heb de volgende tabellen:
leden, lid_id, naam, enz.
team_lid, team_id, lid_id
team, team_id, seizoen_id, naam, enz.

tabel naam
primary key
velden
Opzich is het redelijk ranzig en een subquery had het een stuk makkelijker gemaakt maar ik denk dat er een beter oplossing is

[ Voor 6% gewijzigd door ripexx op 03-04-2004 18:00 ]

[Priet]

Alex de Groot schreef op 03 april 2004 @ 16:53:
Je database indeling is dus eigenlijk net niet gemaakt voor deze actie .

Waarom niet? Waar heb ik een fout gemaakt in het ontwerp? Het werkt prima... met subqueries

Alex de Groot schreef op 03 april 2004 @ 16:53:Kijk eens naar LEFT JOIN, dat is een INNER JOIN maar dan ondergeschikt, als de join geen resultaten krijgt, krijg je toch van de standaard tabel resultaten.

Ik was op de hoogte van JOIN. Maar ik weet absoluut niet meer hoe ik dat in deze situatie moet toepassen. Met kleinere problemen lukt het nog wel, maar volgens mij ben ik hier gewoon te dom voor

@ripexx: Je voorbeeld is mooi, maar ik zou niet weten hoe ik dit moet vertalen naar mijn situatie.

[EfBe]

code:

SELECT  groepen.naam
FROM    (
        groepen INNER JOIN koppel_jaargroepen
        ON 
        groepen.id = koppel_jaargroepen.groep_id
        AND koppel_jaargroepen.schooljaar_naam = '$schooljaar'
    )
    LEFT JOIN koppel_jaargroepengebruikers
    ON 
    koppel_jaargroepen.id = koppel_jaargroepengebruikers.jaargroep_id
    AND koppel_jaargroepengebruikers.gebruiker_id = '$gebruiker_id'
WHERE   koppel_jaargroepengebruikers.id IS NULL

note: het zou fijn zijn als je plaatje op een server staat die aan staat . Ik heb nu gegokt wat de PK is van koppel_jaargroepengebruikers (id). Verder zijn prefixes als 'koppel_' verwarrend. Een tabel koppelt altijd gegevens aan elkaar, nl. de gegevens in de tabel.

Alex de Groot schreef op 03 april 2004 @ 16:53:
Je database indeling is dus eigenlijk net niet gemaakt voor deze actie . Kijk eens naar LEFT JOIN, dat is een INNER JOIN maar dan ondergeschikt, als de join geen resultaten krijgt, krijg je toch van de standaard tabel resultaten.

'ondergeschikt' ? Het is gewoon een andere operatie. De database indeling is prima, zelfs de meest krankjorume schema's zijn te queryen.

[ Voor 31% gewijzigd door EfBe op 04-04-2004 13:03 . Reden: id f1x0red ]

[Priet]

Thanks EfBe Ik had zelf ook iets in elkaar gefrunselt (want natuurlijk weer niet wilde werken.... ), dat zal ik hier ook zo even neergooien. Maar ik zal eerst naar jouw code kijken

M'n host is down Ik zal het schema ergens anders onderbrengen.

/Edit: Mijn erd wordt nu mede mogelijk gemaakt door Planet

/Edit2: MWOOEEEHAAAA HET WERKT!!! Ik heb de code van EfBe gebruikt. Echt snappen doe ik het nog niet, maar ik ga het eens grondig bestuderen en ik hoop dat ik dan snap waarom het juist op deze manier moet.
Er zat één klein foutje in: het is groepen.id en niet groep.groep_id

[ Voor 49% gewijzigd door Priet op 04-04-2004 12:34 ]

[EfBe]

Het werkt als volgt: eerst zorg je voor een lijst van groepen die in het huidige jaar zitten. Dat is de inner join. Daarna ga je aan die lijst vastplakken de koppel_jaargroepgebruiker records. Als die er NIET zijn, moet de rij van de inner join wel blijven bestaan (dat doet de LEFT join). Je bent geinteresseert in de rijen waar er GEEN koppel_jaargroepgebruiker records te vinden waren voor een bepaalde groep. In die rijen is de id van koppel_jaargroepgebruiker NULL. Dus filter je daar op, klaar

[Priet]

Grandioos, het klinkt zo simpel

Ik heb voor mezelf het schema nog eens doorgelopen met die query ernaast. Daarnaast heb ik phpMyAdmin erbij gepakt en de query stap voor stap doorgelopen. Op die manier zie je precies wat er gebeurd

Dus als ik het goed snap: je maakt een grote tabel waarin de groepen voor een jaar staan. Die 'hang' je aan een tabel waarin ook de gebruikers voor de groepen van dat schooljaar instaan. Als de koppeling niet bestaat dan is NULL zichtbaar voor de velden die niet bestaan.
Pfff, je moet er maar opkomen

[EfBe]

Is standaard mechanisme voor dit soort vraagstukken als je leest "ik wil selecteren op iets dat NIET ABC heeft" dan weet je dat je ergens left join moet toepassen en op NULL moet filteren.

[Priet]

Weer wat geleerd

Wat geniet de voorkeur: een query met 'WHERE - AND' (zoals het eerste codevoorbeeld in eerste post) of een query met '(INNER) JOIN' (zoals in het tweede codevoorbeeld)?

Ik heb even in phpMyAdmin getest en de tijd om uit te voeren is in beide gevallen 0.0008 seconden

[ Voor 26% gewijzigd door Priet op 04-04-2004 15:51 ]

[Alex]

Priet schreef op 04 april 2004 @ 15:50:
Weer wat geleerd

Wat geniet de voorkeur: een query met 'WHERE - AND' (zoals het eerste codevoorbeeld in eerste post) of een query met '(INNER) JOIN' (zoals in het tweede codevoorbeeld)?

Ik heb even in phpMyAdmin getest en de tijd om uit te voeren is in beide gevallen 0.0008 seconden

Met WHERE kun je dergelijke joins niet doen, als je toch wil joinen kun je het beste wel de JOIN Syntax doen anagezien die imo net ietsjes duidelijker is.

[EfBe]

Priet schreef op 04 april 2004 @ 15:50:
Weer wat geleerd

Wat geniet de voorkeur: een query met 'WHERE - AND' (zoals het eerste codevoorbeeld in eerste post) of een query met '(INNER) JOIN' (zoals in het tweede codevoorbeeld)?

Ik heb even in phpMyAdmin getest en de tijd om uit te voeren is in beide gevallen 0.0008 seconden

De een is comform de ansi join syntax (INNER / LEFT) de ander niet (FROM a, b, c). Met de non-ansi joins moet je in de where aangeven welke kant LEFT is bij een left join anders is het altijd een INNER join (of bij slechte db's een cross join). Bv:
SELECT * FROM A LEFT JOIN B ON A.Foo = B.Foo
is gelijk aan op Oracle:
SELECT * FROM A, B WHERE A.Foo = B.Foo(+)

(als ik me niet vergis, ik vergeet altijd wara die (+) moet komen te staan). Geen idee of MySql dat ook heeft.