query voor het juist NIET selecteren van records

[TromboneFreakus]

Voor de ledenadministratie van onze faculteitsvereniging heb ik een database gebouwd. Voor dit topic zijn daarbij de volgende tabellen van belang:

[b]Ledenlijst[/b]
Velden van belang: LidID, achternaam, rekeningnummer

[b]Collegejaren[/b]
Velden:CollegejaarID, Collegejaar

[b]Betaalde_collegejaren[/b]
Velden: ID, CollegejaarID, LidID

In de laatste tabel komt in feite alles samen, want de velden CollegejaarID en LidID worden gelinkt aan de vorige twee tabellen.

Het probleem nu is het volgende. Voor de incasso wil ik een query schrijven die resulteert in de leden die dat collegejaar nog NIET betaald hebben. Ik heb al allerlei query's kunnen produceren, zoals natuurlijk die van welke leden wel betaald hebben (die is ook niet moeilijk), leden die uberhaupt nog nooit betaald hebben, etc. etc.

Het zit 'em ergens in het plaatsen van de voorwaarde 'is null' of 'is not null' en de richting van de JOIN, maar ik zie het even niet. En nu staar ik me er blind op.

Iemand wellicht een idee?

[ColdSTone|IA]

Denk even goed na over wat je dus precies voor gegevens wilt hebben
Als ik 't goed heb wil je dus alle leden hebben die wel in de ledenlijst voorkomen maar waarvan het lidID in combinatie met het huidige collegejaar niet voorkomt in de tabel Betaalde_collegejaren?

[whoami]

Je zult het met een subquery moeten:

code:

select * from tabel
where id not in ( select id from anderetabel)

[TromboneFreakus]

ColdSTone schreef op vrijdag 03 december 2004 @ 13:03:
Denk even goed na over wat je dus precies voor gegevens wilt hebben
Als ik 't goed heb wil je dus alle leden hebben die wel in de ledenlijst voorkomen maar waarvan het lidID in combinatie met het huidige collegejaar niet voorkomt in de tabel Betaalde_collegejaren?

Precies, dat is het kort en bondig omschreven.

Dus er moet op basis van een te kiezen collegejaar door Access gemeld worden welke leden dan nog NIET betaald hebben.

[Glewellyn]

TromboneFreakus schreef op vrijdag 03 december 2004 @ 12:57:
Voor de ledenadministratie van onze faculteitsvereniging heb ik een database gebouwd. Voor dit topic zijn daarbij de volgende tabellen van belang:

[b]Ledenlijst[/b]
Velden van belang: LidID, achternaam, rekeningnummer

[b]Collegejaren[/b]
Velden:CollegejaarID, Collegejaar

[b]Betaalde_collegejaren[/b]
Velden: ID, CollegejaarID, LidID

In de laatste tabel komt in feite alles samen, want de velden CollegejaarID en LidID worden gelinkt aan de vorige twee tabellen.

Het probleem nu is het volgende. Voor de incasso wil ik een query schrijven die resulteert in de leden die dat collegejaar nog NIET betaald hebben. Ik heb al allerlei query's kunnen produceren, zoals natuurlijk die van welke leden wel betaald hebben (die is ook niet moeilijk), leden die uberhaupt nog nooit betaald hebben, etc. etc.

Het zit 'em ergens in het plaatsen van de voorwaarde 'is null' of 'is not null' en de richting van de JOIN, maar ik zie het even niet. En nu staar ik me er blind op.

Iemand wellicht een idee?

Kan je hier iets mee?

code:

SELECT
    *
FROM
    ledenlijst
WHERE
    lidid NOT IN (
    SELECT DISTINCT
        lidid
    FROM
        betaalde_collegejaren
     WHERE
        collegejaarid = <collegejaarid van het jaar dat je zoekt>
     )

[Infinitive]

Eventueel zou een left outer join ook kunnen als je filtert op het ontbreken (NULL zijn) van de attributen van de tweede tabel. Een subquery is echter duidelijker.

[Verwijderd]

Als je een left join doet van Ledenlijst.LidID op Betaalde_collegejaren.LidID en dan filtert op CollegeJaar en null/leeg CollegeJaarID dan zou je de niet betaalde terug moeten krijgen.

[Janoz]

Ik zie wel een groot probleem met deze structuur. Ik neem aan dat studenten niet altijd lid zijn van de vereniging? Je slaat nu op welk jaar betaald is, maar nergens welk jaar een student daadwerkelijk lid is.

[TromboneFreakus]

whoami schreef op vrijdag 03 december 2004 @ 13:04:
Je zult het met een subquery moeten:

code:

select * from tabel
where id not in ( select id from anderetabel)

Toepast op deze casus bedoel je dus:

code:

SELECT * From Ledenlijst WHERE 
LidID NOT IN (SELECT LidID FROM Betaalde_Collegejaren);

Dat werkt als query wel, maar dat selecteert alle leden die nooit betaald hebben. Die had ik ook al - overigens veel ingewikkelder, dus in die zin bedankt voor het inzicht - maar is dus niet wat ik zoek.

[Verwijderd]

Janoz schreef op vrijdag 03 december 2004 @ 13:11:
Ik zie wel een groot probleem met deze structuur. Ik neem aan dat studenten niet altijd lid zijn van de vereniging? Je slaat nu op welk jaar betaald is, maar nergens welk jaar een student daadwerkelijk lid is.

Misschien vond ie dat niet relevant voor het probleem?

[TromboneFreakus]

Glewellyn schreef op vrijdag 03 december 2004 @ 13:07:
[...]


Kan je hier iets mee?

code:

SELECT
    *
FROM
    ledenlijst
WHERE
    lidid NOT IN (
    SELECT DISTINCT
        lidid
    FROM
        betaalde_collegejaren
     WHERE
        collegejaarid = <collegejaarid van het jaar dat je zoekt>
     )

Euhh.... dit levert de mensen op die in het collegejaar niet betaald hebben, maar ook in de collegejaren daarvoor en daarna als ze toen niet betaald hadden. DUs dit levert een groot probleem op voor de nieuwe leden, die dus lid werden na een collegejaar dat al in de tabel staat.

Dat is het lastige van deze constructie, dat geef ik meteen toen. Daarom liep ik tot nu toe ook vast...

[TromboneFreakus]

Verwijderd schreef op vrijdag 03 december 2004 @ 13:12:
[...]


Misschien vond ie dat niet relevant voor het probleem?

Inderdaad, wie niet lid is wordt verwijderd. Dus wie niet betaalt, is niet langer lid. Dat zou immers het faillissement van de vereniging betekenen...

[Glewellyn]

TromboneFreakus schreef op vrijdag 03 december 2004 @ 13:13:
[...]


Euhh.... dit levert de mensen op die in het collegejaar niet betaald hebben, maar ook in de collegejaren daarvoor en daarna als ze toen niet betaald hadden. DUs dit levert een groot probleem op voor de nieuwe leden, die dus lid werden na een collegejaar dat al in de tabel staat.

Dat is het lastige van deze constructie, dat geef ik meteen toen. Daarom liep ik tot nu toe ook vast...

En er ligt niet ergens vast wanneer iemand lid is geworden?

[TromboneFreakus]

Glewellyn schreef op vrijdag 03 december 2004 @ 13:19:
[...]


En er ligt niet ergens vast wanneer iemand lid is geworden?

Jawel, in de tabel ledenlijst wordt ook naar het collegejaarId verwezen waarin iemand lid is geworden.

[Verwijderd]

code:

SELECT
    *
FROM
    Ledenlijst AS L
LEFT  JOIN
    Betaalde_collegejaren AS B ON L.LidID = B.LidID
INNER JOIN
    Collegejaren AS C ON B.CollegejaarID = C.CollegejaarID 
WHERE
    C.Collegejaar = <collegejaar van het jaar dat je zoekt>
AND
    B.CollegejaarID IS NULL

Zoiets ...?

[ Voor 7% gewijzigd door Verwijderd op 03-12-2004 13:34 . Reden: Ook goed... ]

[ACM]

Je zult toch echt bij moeten houden wanneer iemand lid geworden is en evt wanneer ie weer lid-af is. Zo niet, dan kan je nooit bepalen wanneer ie wel of niet had moeten betalen.

Als je het wel bijhoudt, kan het met zoiets:

code:

SELECT * FROM
leden l NATURAL JOIN jaren sj
WHERE NOT EXISTS 
    ( SELECT * FROM betalingen b NATURAL JOIN jaren j 
         WHERE j.jaartal >= sj.jaartal AND b.lidid = l.lidid)
AND
sj.jaartal >= 2004

Dat is voor iemand die nooit betaald heeft.

Ik geloof dat je die interne j.jaartal >= sj.jaartal ook weg kan laten


Mookai: het is op zijn minst IS NULL, ipv = NULL

[ Voor 26% gewijzigd door ACM op 03-12-2004 13:33 ]

[ACM]

Iemand die niet elk jaar betaald heeft kan beter zo denk ik:

code:

SELECT * FROM
leden l NATURAL JOIN jaren sj
WHERE 
    ( SELECT count(*) FROM betalingen b WHERE b.lidid = l.lidid)
    <
    ( SELECT count(*) FROM jaren j WHERE j.jaartal >= sj.jaartal)

[ Voor 8% gewijzigd door ACM op 03-12-2004 13:35 ]

[Janoz]

Ik zou de koppeltabel gewoon gebruiken om aan te geven dat de persoon dat jaar lid was. Voeg daar ene bit/boolean veld toe die aangeeft of er is betaald en klaar.

Het verwijderen van een lid lost het probleem niet op. Zodra iemand lid is zou deze voor alle jaren dat er leden zijn betaald moeten zijn. Daarnaast kan iemand 4 jaar lid zijn en zijn al zijn schulden kwijtgescholden waneer hij het lidmaatschap opzegt.

[TromboneFreakus]

Janoz schreef op vrijdag 03 december 2004 @ 13:39:
Ik zou de koppeltabel gewoon gebruiken om aan te geven dat de persoon dat jaar lid was. Voeg daar ene bit/boolean veld toe die aangeeft of er is betaald en klaar.

Het verwijderen van een lid lost het probleem niet op. Zodra iemand lid is zou deze voor alle jaren dat er leden zijn betaald moeten zijn. Daarnaast kan iemand 4 jaar lid zijn en zijn al zijn schulden kwijtgescholden waneer hij het lidmaatschap opzegt.

Heel scherp, dank je wel. Ik was inderdaad bezig met het toevoegen van alle collegejaren uit het verleden om in te stellen dat iedereen dan betaald had, maar dat resulteert in waanzinnig veel records en bovendien in een rare situatie bij nieuwe leden.

-------
Mmm, daar kom ik op terug. Dankzij het inzicht hier verkregen, werkt het toch heel simpel:

code:

SELECT * FROM Ledenlijst 
WHERE (Ledenlijst.LidID) Not In (SELECT LidID FROM [Betaalde collegejaren] WHERE CollegejaarID=4);

Zolang er immers maar per collegejaar geincasseerd hoeft te worden, maakt dit voor het verleden toch niet uit.

Of maak ik nu een denkfout?

[ Voor 21% gewijzigd door TromboneFreakus op 06-12-2004 13:47 ]

[Verwijderd]

Ik ga er maar even vanuit dat jij denkt
Jaar 1: eerste jaar dat iemand studeert
Jaar 2: tweede jaar
Enz.

Bij modelstudenten ben je dan met 4-6 jaar klaar. Helaas is niet iedereen een model student.
Ook kun je lastig zien of iemand al in zijn 3de, 4de of x-de lidmaatschapsjaar zit.
Moet iemand die in zijn 2de studiejaar zit ook al zijn 3de jaar betaald hebben?
En zo zijn er nog wel wat rare zaken te bedenken waar je rekening mee moet houden
Logisch lijkt mij

Select mensen from table where lid_ID not in (select Lid_ID from contributie where jaar_betaald=true and jaar=2004)

Bij het begin van het jaar voeg je een record toe voor iedereen die in 2004 lid wil zijn met jaar_betaald op false. Bij een betaling wijzig je dat naar true.

(Eenvoudiger en sneller is dan natuurlijk:
select achternaam from ledentabel, contributietabel where mensen.lidID=contributietabel.lidID AND contributietabel.jaar_betaald=false)

En ja, dan krijg je een extra boolean veld in je betaalde contributietabel. Maar denk positief. Het ID veld is eigenlijk overbodig aangezien de key, CollegejaarID, LidID ook uniek is. Je tabel neemt dan zelfs minder ruimte in beslag

[ Voor 25% gewijzigd door Verwijderd op 07-12-2004 04:05 ]

[Verwijderd]

Wat jij wilt, kan simpelweg niet met deze structuur. Dit komt omdat er geen andere koppeling tussen een lid en zijn collegejaar is anders dan de tabel betaalde_collegejaren.

Dit is eenvoudig op te lossen door de database betaalde_colegejaren te veranderen naar bv. status_betaling, en dan een boolean betaald toe toe voegen.

[ Voor 31% gewijzigd door Verwijderd op 07-12-2004 03:10 ]

[TromboneFreakus]

Verwijderd schreef op dinsdag 07 december 2004 @ 03:03:
Ik ga er maar even vanuit dat jij denkt
Jaar 1: eerste jaar dat iemand studeert
Jaar 2: tweede jaar
Enz.

Even voor de helderheid: de collegejaren staan in een aparte tabel, volgens deze structuur:

CollgejaarID Collegejaar
1 1996/1997
2 1997/1998
3 1998/1999
enz.

Dat ID wordt vervolgens gebruikt om op te slaan in welk collegejaar iemand lid is geworden (puur informatief, voor incasso niet relevant) en om op te slaan in welke collegejaar een bepaald lid (LidID) betaald heeft.

Juist door met deze query te werken, gaat het volgens mij

code:

SELECT * FROM Ledenlijst 
WHERE (Ledenlijst.LidID) Not In (SELECT LidID FROM [Betaalde collegejaren] WHERE CollegejaarID=4);

Immers, zo laat ik Access alle leden selecteren die nog niet vermeld staan als zijnde betaald in de tabel Betaalde collegejaren voor een bepaald collegejaar.

Volgens mij is het al met al toch vrij simpel (ondanks de moeilijkheden die ik eerst had, bedankt voor het verschafte inzicht!), of zie ik het nu verkeerd?

[Janoz]

TromboneFreakus schreef op dinsdag 07 december 2004 @ 11:42:

Dat ID wordt vervolgens gebruikt om op te slaan in welk collegejaar iemand lid is geworden (puur informatief, voor incasso niet relevant) en om op te slaan in welke collegejaar een bepaald lid (LidID) betaald heeft.

Niet relevant? Hier zit juist de huge denkfout die je maakt. Het college jaar is wel degelijk van belang om aan te geven of iemand nog niet betaald had of of iemand gewoon nog geen student was. Zolang je dat gegeven nergens gebruikt is je implementatie brak.

[Verwijderd]

TromboneFreakus schreef op dinsdag 07 december 2004 @ 11:42:
Dat ID wordt vervolgens gebruikt om op te slaan in welk collegejaar iemand lid is geworden (puur informatief, voor incasso niet relevant) en om op te slaan in welke collegejaar een bepaald lid (LidID) betaald heeft.

Hier schrijf je zelf je probleem al op. Je gebruikt het collegejaar alleen op op te slaan wie er wel betaald heeft. Terwijl je wilt weten wie er niet betaald heeft.

Dat kan dus niet. Je zult dan altijd mensen krijgen die in ALLE jaren niet betaald hebben etc, aangezien er aan het feit 'niet betalen' geen jaar hangt.

SELECT * FROM Ledenlijst
WHERE (Ledenlijst.LidID) Not In (SELECT LidID FROM [Betaalde collegejaren] WHERE CollegejaarID=4);

Helaas.. stel dat ik het collegejaar met id 2 niet betaald. Daarna ben ik gestopt met de studie. Dan kom ik met bovenstaande query gewoon mee, terwijl jij alleen mensen uit het collegejaar met id 4 had willen hebben.

[ Voor 23% gewijzigd door Verwijderd op 07-12-2004 15:45 ]

[TromboneFreakus]

Verwijderd schreef op dinsdag 07 december 2004 @ 15:36:
[...]

Helaas.. stel dat ik het collegejaar met id 2 niet betaald. Daarna ben ik gestopt met de studie. Dan kom ik met bovenstaande query gewoon mee, terwijl jij alleen mensen uit het collegejaar met id 4 had willen hebben.

Stel dat je toen niet betaald hebt, dan sta je niet vermeld in die tabel (van betaalde collegejaren). Dankzij de WHERE-clausule in de query word je dan ook niet getoond, als ik het wel heb.

SELECT * FROM Ledenlijst
WHERE (Ledenlijst.LidID) Not In (SELECT LidID FROM [Betaalde collegejaren] WHERE CollegejaarID=4);

Als ik de query 'vertaal', dan staat er het volgende: selecteer alles uit de tabel ledenlijst, behalve die leden die al hebben betaald in het collegejaar met ID 4.

Wanneer dat dus het huidige collegejaar is, worden de mensen die contact betaald hebben gefilterd en de rest wordt eruit gespuwd.

Hier zijn wel enkele assumpties natuurlijk:
- iedereen vermeld in de database is lid;
- wie niet betaalt, wordt verwijderd;
- wie niet betaalde, is verwijderd.

Maar gezien de overtuigende reactie van meer van jullie maak ik vast nog steeds een denkfout. Als me de ogen geopend kunnen worden dan graag natuurlijk.

[Janoz]

Als je begint ze zelf eens op en te doen en de hier geboden alternatieven eens uitwerkt...

Ga voor de grap eens de queries in elkaar zetten die je zou moeten gebruiken waneer je Betaalde_collegejaren hernoemd naar lid_collegejaren en hierin een boolean betaald toevoegd.

Ik geef je alvast een opzetje:

SELECT * FROM ledenlijst l JOIN lid_collegejaar lc on l.id = lc.lidid WHERE betaald=false;