[SQL] Probleem met verschillende gekoppelde waardes

[Verwijderd]

Ik heb een tabel met de naam EXEMPLAREN waarin al mijn boeken staan met een exnr (exemplaarnummer) en een boeknr (boeknummer). Nu zijn er ook een aantal boeken uitgeleend. Die staan in de tabel UITLENINGEN. In die tabel staat van de uitgeleende boeken het boeknr en het exnr. Nu wil ik met een SELECT opdracht de boeken te zien krijgen die niet uitgeleend zijn. Ik had zelf zoiets bedacht:

code:

SELECT boeknr, exnr
FROM EXEMPLAREN
WHERE boeknr, exnr NOT IN
(SELECT boeknr, exnr
FROM UITLENINGEN);

Maar dit werkt niet. Kan iemand mij helpen?

[Pyrus]

Als ik me niet vergis snapt SQL het niet als je in een subquery twee kolommen selecteert. Is opzich ook niet zo vreemd, want hoe moet de engine nou weten welke van de twee kolommen hij op moet vergelijken?
Misschien kun je je datamodel zo aanpassen dat de exemplaarnummers uniek worden en dus niet afhankelijk van het boeknummer
Geen idee of de studentassistenten dat leuk vinden, maar geloof dat ik vorig jaar ook zoiets gedaan heb

[Verwijderd]

Bij NOT IN (SELECT ...) kun je maar op 1 veld checken.
Wat je hier nodig hebt is een join, en dan kijken of 't record niet in UITLENINGEN voorkomt.

[Verwijderd]

Ik heb het nu een beetje flauw opgelost. Het was de bedoeling dat je bijvoorbeeld had: boek 1, ex 1
boek 1, ex 2
boek 2, ex 1
boek 2, ex 2

Maar nu heb ik het zo gedaan dan er geen identieke exemplaarnummers meer zijn. En dan kan ik gewoon kijken of het exemplaarnummer niet voorkomt bij UITLENINGEN en dan zeggen: deze boeken zijn beschikbaar.

Bedankt voor jullie hulp!

[MrBucket]

Pyrus schreef op zondag 07 oktober 2007 @ 21:34:
Misschien kun je je datamodel zo aanpassen dat de exemplaarnummers uniek worden en dus niet afhankelijk van het boeknummer
Geen idee of de studentassistenten dat leuk vinden, maar geloof dat ik vorig jaar ook zoiets gedaan heb

Mee eens.

Gebruik in de tabel Exemplaren in plaats van een samengestelde primary key (boeknr, exnr) een gegenereerd primary key veld (exemplaarId), en maak van (boeknr, exnr) een alternate key (m.a.w.: maak een unique constraint aan op deze twee velden).

In de tabel Uitleningen gebruik je vervolgens het exemplaarId (in plaats van boeknr, exnr) om bij te houden welke exemplaren zijn uitgeleend. Dan kun je wel de WHERE ... NOT IN (...) constructie gebruiken.

Sowiezo zijn samengestelde primary keys vaak onhandig bij het schrijven van queries.

[Verwijderd]

Klinkt goed, MrBucket. Ik werk in MS Acces en voer daar de tabellen en de selecteerquery's in. Hoe kan ik daar zon alternate key maken? Ik weet natuurlijk wel hoe een primaire sleutel moet, maar dat is weer wat anders dan een alternate key.

[_js_]

Je kunt ipv NOT IN(...) ook NOT EXISTS (...) gebruiken. Syntax is iets anders, maar dat kun je opzoeken. Met NOT EXISTS kun je wel werken met samengestelde sleutels.

Hoe je een index in Access maakt kun je in de help van Access vinden.

[dik_voormekaar]

Ik zou in de tabel EXEMPLAREN gewoon een veld "uitgeleend" hebben toegevoegd.
Dan kan de tabel UITLENINGEN vervallen.

[Acid__Burn]

dik_voormekaar schreef op maandag 08 oktober 2007 @ 09:48:
Ik zou in de tabel EXEMPLAREN gewoon een veld "uitgeleend" hebben toegevoegd.
Dan kan de tabel UITLENINGEN vervallen.

Dat zou de mooiste oplossing zijn denk ik. Maar een JOIN o.i.d. is ook een oplossing. Het ligt een beetje aan het model dat je gebruikt. Ben je van plan om voor alles andere tabellen te maken (normaliseren) dan zal je dus de JOIN e.d. moeten bekijken...

[Janoz]

dik_voormekaar schreef op maandag 08 oktober 2007 @ 09:48:
Ik zou in de tabel EXEMPLAREN gewoon een veld "uitgeleend" hebben toegevoegd.
Dan kan de tabel UITLENINGEN vervallen.

Dat zou ik juist niet doen. Door de uitleiningen appart te houden kun je meer gegevens bijhouden. Oa sinds wanneer hij al uitgeleend is en aan wie je dat gedaan hebt. Geeft je ook een einddatum op dan kun je vervolgens ook nog een geschiedenis bijhouden.

Gecombineerde ID zou ik ook ditchen. Gewoon een autonummer voor de exemplaren tabel en daarin dus enkel een foreign key naar boeken. De uitleen tabel heeft vervolgens een start en eind datum samen met een exemplaar id. Een uitgeleend exemplaar is dan een exemplaar waarbij de gejoinde tabellen een terugbreng datum heeft die null is.

[Martin Sturm]

_js_ schreef op zondag 07 oktober 2007 @ 23:42:
Je kunt ipv NOT IN(...) ook NOT EXISTS (...) gebruiken. Syntax is iets anders, maar dat kun je opzoeken. Met NOT EXISTS kun je wel werken met samengestelde sleutels.

Hoe je een index in Access maakt kun je in de help van Access vinden.

Dit is inderdaad de beste methode. Je maakt dan gebruik van het feit dat het gewoon twee sets zijn (de boeken en de boeken die uitgeleend zijn) waarvan je het verschil wilt hebben. Andere manieren zijn imo niet erg netjes en eigenlijk een houtje-toutje oplossing.

[BikkelZ]

Een vies trucje (in iig in MySQL mogelijk) is een LEFT JOIN doen waarbij er gekeken wordt of een kolom IS NULL is die nooit NULL kan zijn.

Dus:

SQL:

SELECT *
FROM table_1 t1
LEFT JOIN table_2 t2 ON t1.key1 = t2.foreign_key1 AND t1.key2 = t2.foreign_key2
WHERE t2.foreign_key1 IS NULL

Dan retourneren dus uitsluitend die rijen die geen match hebben op de tweede table, en daarom alleen NULL-waardes voor die kolommen hebben.

[Acid__Burn]

BikkelZ schreef op maandag 08 oktober 2007 @ 10:05:
Een vies trucje (in iig in MySQL mogelijk) is een LEFT JOIN doen waarbij er gekeken wordt of een kolom IS NULL is die nooit NULL kan zijn.

Dus:

SQL:

SELECT *
FROM table_1 t1
LEFT JOIN table_2 t2 ON t1.key1 = t2.foreign_key1 AND t1.key2 = t2.foreign_key2
WHERE t2.foreign_key1 IS NULL

Dan retourneren dus uitsluitend die rijen die geen match hebben op de tweede table, en daarom alleen NULL-waardes voor die kolommen hebben.

Dit werkt in MSSQL ook. Dat zou een nog mooiere oplossing zijn. Maar je moet zeker kijken naar de verschillende oplossingen, i.v.m. performance. Ligt er natuurlijk aan waarvoor je het wilt gaan gebruiken, maar als je dit bijvoorbeeld verwerkt in een web applicatie dan is de performance súper belangrijk

[MrBucket]

Martin Sturm schreef op maandag 08 oktober 2007 @ 10:03:
[...]

Dit is inderdaad de beste methode. Je maakt dan gebruik van het feit dat het gewoon twee sets zijn (de boeken en de boeken die uitgeleend zijn) waarvan je het verschil wilt hebben. Andere manieren zijn imo niet erg netjes en eigenlijk een houtje-toutje oplossing.

Ik neem aan dat je met "houtje touwtje" bedoelt: het aanpassen van je datamodel om je queries makkelijker te maken?

Er is namelijk niks mis met het gebruik van een enkele autoincrement kolom als primary key, en je identificerende gegevens middels een unique constraint af te dwingen.

[Sepio]

_js_ schreef op zondag 07 oktober 2007 @ 23:42:
Je kunt ipv NOT IN(...) ook NOT EXISTS (...) gebruiken. Syntax is iets anders, maar dat kun je opzoeken. Met NOT EXISTS kun je wel werken met samengestelde sleutels.

Hoe je een index in Access maakt kun je in de help van Access vinden.

Een voorbeeld hiervan is:

SQL:

SELECT boeknr, exnr
FROM EXEMPLAREN a
WHERE NOT EXISTS (
    SELECT *
    FROM UITLENINGEN b
    WHERE a.boeknr = b.boeknr
    AND   a.exnr = b.exnr
)

[Verwijderd]

in Oracle lukt dit dacht'k, voor access weet ik het niet:

code:

SELECT boeknr, exnr
FROM EXEMPLAREN
WHERE (boeknr, exnr) NOT IN
(SELECT boeknr, exnr
FROM UITLENINGEN);

door die haakjes begin je dan toch 'records' te vergelijken

[KabouterSuper]

In Oracle werkt dit zeker....de haakjes doen het hem. Je kunt ook een MINUS gebruiken in Oracle...net zo gemakkelijk

code:

SELECT boeknr, exnr FROM EXEMPLAREN
MINUS
SELECT boeknr, exnr FROM UITLENINGEN

Dit moet ongetwijfeld in andere databases ook kunnen (MySQL5 bijvoorbeeld)

[d00d]

In SQL Server (vanaf 2005) is dit ook mogelijk met de EXCEPT operator. Ook nieuw is de INTERSECT operator om identieke rows uit twee sets te genereren. Heeft Oracle ook zoiets?

Wel jammer dat de grote jongens het niet eens worden over de syntax van dit soort statements.

code:

SELECT boeknr, exnr FROM EXEMPLAREN
EXCEPT
SELECT boeknr, exnr FROM UITLENINGEN

[KabouterSuper]

EXCEPT is identiek aan MINUS (vanaf Oracle 7.1 geimplementeerd, beide varianten mogen in O.)
INTERSECT bestaat ook in Oracle.

Overigens meen ik te weten dat EXCEPT en INTERSECT shorthand notatie is voor NOT EXIST en EXIST.

[ Voor 57% gewijzigd door KabouterSuper op 09-10-2007 11:50 ]