[SQL] Complexe query over meerdere tabellen *

[loodgieter]

Ik heb een probleem met SQL queries.

Ik heb een database met de volgende tabellen
---------------------------------------------
create table KlantGeg
(Sofinummer character (9) NOT NULL,
Naam varchar(30) NOT NULL,
Huisnr varchar(6) NOT NULL,
Postcode char(6) NOT NULL,
Telefoonnummer char(10),
Leeftijd char(3),
PRIMARY KEY (Sofinummer),
FOREIGN KEY (Postcode) REFERENCES Locatie(Postcode) );


create table Boeken
(ISBNnr char(10) NOT NULL,
Titel varchar(100) NOT NULL,
PRIMARY KEY (ISBNnr) );


create table BoekUitleen
(Plaatsnr integer NOT NULL,
Uitleendatum date NOT NULL,
ISBNnr char(10) NOT NULL,
PRIMARY KEY (Plaatsnr),
FOREIGN KEY (ISBNnr) REFERENCES Boeken(ISBNnr) );


create table KoppelTabel
(Sofinummer character (9) NOT NULL,
Plaatsnr integer NOT NULL,
FOREIGN KEY (Plaatsnr) REFERENCES BoekUitleen(Plaatsnr),
FOREIGN KEY (Sofinummer) REFERENCES KlantGeg(Sofinummer) );
----------------------------------------------

nu wil ik een sql query uitvoeren voor een klant die alle boeken heeft uitgeleend. Ik heb al verschillende query's geprobeerd waaronder de onderstaande :

----------------------------------------------
Select KlantGeg.Naam
From KlantGeg, Koppeltabel
Where KlantGeg.Sofinummer = Koppeltabel.sofinummer
and exists (
select Koppeltabel.Plaatsnr
from Koppeltabel, Boekuitleen
where Boekuitleen.plaatsnr = Koppeltabel.plaatsnr
and exists (
select boekuitleen.isbnnr
from BoekUitleen, Koppeltabel
where Boekuitleen.Plaatsnr = Koppeltabel.plaatsnr
and exists (
select Boeken.Isbnnr
from Boeken, Boekuitleen
where Boeken.Isbnnr = Boekuitleen.isbnnr
)))
Group by KlantGeg.Naam;

-----------------------------------

Deze query geeft echter alle klanten die gewoon boeken hebben uitgeleend. Heeft iemand er hier verstand van? Ik heb alle mogelijkheden volgens mij geprobeerd maar het lukt me echt niet.

excuus voor de rare titel

[ Voor 1% gewijzigd door loodgieter op 22-01-2004 14:10 . Reden: titel ]

[BoomSmurf]

Wil je nou een query van die éne klant die ALLE boeken thuis heeft liggen? Lijkt me niet echt een real world query

[loodgieter]

Het is ook niet een echte real world query.. maar een opdracht voor school. We komen er echt niet meer uit, misschien hebben we de database verkeerd opgebouwd?

BoomSmurf schreef op 22 januari 2004 @ 14:07:
Wil je nou een query van die éne klant die ALLE boeken thuis heeft liggen? Lijkt me niet echt een real world query

[Nielsz]

Join klantgegevens met de koppeltabel (what's in a name), en doe een count hoeveel boeken hij heeft. Return dan alle klantgegevens waar dat aantal hetzelfde is als het totaal aantal boeken?

[Verwijderd]

Gebruik

code:

COUNT( DISTINCT veldnaam )

[BoomSmurf]

- pvd heb 'em gequote een stuk verder -

[ Voor 124% gewijzigd door BoomSmurf op 22-01-2004 14:19 ]

[Verwijderd]

Wat hierboven staat werkt in ieder geval niet. Wat "exists" precies doet, weet ik niet, maar je koppelt in de tabelen niet in de subqueries, waardoor je bij lange niet het resultaat krijgt dat je wilt hebben.

Ik zal eens kijken hoe het eruit zou moeten zien.

[loodgieter]

Deze gaat niet werken want een klant kan vaker eenzelfde boek lenen

Nielsz schreef op 22 januari 2004 @ 14:11:
Join klantgegevens met de koppeltabel (what's in a name), en doe een count hoeveel boeken hij heeft. Return dan alle klantgegevens waar dat aantal hetzelfde is als het totaal aantal boeken?

[curry684]

Twix schreef op 22 januari 2004 @ 14:04:
excuus voor de rare titel

Maak dan een goede ipv je ervoor te verexcuseren

[loodgieter]

Jah klopt... het werkt niet goed... hij laat inderdaad alle klanten zien die een boek hebben uitgeleend. Toch zitten we aardig in de knoop.

Verwijderd schreef op 22 januari 2004 @ 14:12:
Wat hierboven staat werkt in ieder geval niet. Wat "exists" precies doet, weet ik niet, maar je koppelt in de tabelen niet in de subqueries, waardoor je bij lange niet het resultaat krijgt dat je wilt hebben.

Ik zal eens kijken hoe het eruit zou moeten zien.

[Spidey]

Of zoiets van

select klant
from klant, koppel
where.... <join de handel>
having count(koppel) = select count(*) from boeken


edit:
het is en blijft school, dus wel zelf nadenken!

[ Voor 27% gewijzigd door Spidey op 22-01-2004 14:19 ]

[Verwijderd]

code:

-- 'pseudo' SQL
select k.klant_id 
from klant k
inner join geleende_boeken g on joinvelden
group by k.klant_id
having count( distinct boek_id ) = (select count(*) from boeken)

[BoomSmurf]

BoomSmurf schreef op 22 januari 2004 @ 14:11:
[...]


of...

code:

SELECT 
  * 
FROM 
  KLANTGEG
WHERE
  ((SELECT COUNT(ISBNNR) FROM BOEKEN) = 
     (SELECT COUNT(PLAATSNR) FROM BOEKUITLEEN)) AND 
  (SELECT COUNT(DISTINCT SOFINUMMER) FROM KOPPELTABEL) = 1 AND 
  EXISTS(SELECT DISTINCT SOFINUMMER FROM KOPPELTABEL WHERE SOFINUMMER = KLANTGEG.SOFINUMMER)

Dit kan beter natuurlijk, maar het gaat om het idee erachter

[ Voor 7% gewijzigd door BoomSmurf op 22-01-2004 14:20 ]

[Verwijderd]

Je vraagstelling is bovendien behoorlijk vaag.
Wil je een overzicht van de klant en alle boeken die hij heeft geleend?

In dat geval:

SELECT KlantGeg.Naam, BoekUitleen.ISBNnr
FROM KlantGeg INNER JOIN (KoppelTabel INNER JOIN BoekUitleen ON KoppelTabel.Plaatsnr = BoekUitleen.Plaatsnr) ON KlantReg.Sofinummer = KoppelTabel.Sofinummer
ORDER BY KlantGeg.Naam

Als je een lijst wilt hebben van alle klanten die een boek hebben geleend:

SELECT DISTINCT KlantGeg.Naam
FROM KlantGeg INNER JOIN (KoppelTabel INNER JOIN BoekUitleen ON KoppelTabel.Plaatsnr = BoekUitleen.Plaatsnr) ON KlantReg.Sofinummer = KoppelTabel.Sofinummer
ORDER BY KlantGeg.Naam

[loodgieter]

Hey harstikke bedankt voor de query! Hij werkt goed!! Thanxx!!

BoomSmurf schreef op 22 januari 2004 @ 14:19:
[...]

[Maasluip]

* Maasluip is with Spidey73 en TheChkU.
Ik weet alleen niet of je SQL (je zegt niet of het MySql of SQLserver of Oracle of... is) dat pikt, die subselect in de having.

Maar verder had ik een tabel minder gemaakt, namelijk het sofinummer in de boekuitleen tabel gezet. Dan kun je die koppeltabel strepen.
Dan ga je dan nog altijd niet tegen een normaalvorm in. Een tabel die alleen maar foreign keys heeft is volgens mij meestal overbodig (maar ik moet zeggen dat ik dit even neerleg zonder onderbouwing)

edit:
k*t. sneller tikken

[ Voor 5% gewijzigd door Maasluip op 22-01-2004 14:26 ]

[Spidey]

Weer iemand die in de WW belandt omdat ie liever lui dan moe is!

[BoomSmurf]

Twix schreef op 22 januari 2004 @ 14:23:
Hey harstikke bedankt voor de query! Hij werkt goed!! Thanxx!!


[...]

U knaagt, wij aaien.

[loodgieter]

De query werkt perfect!! Alleen oorspronkelijk was de opdracht om 2x exist resp. not exist te gebruiken. Maar volgens mij is dit niet mogelijk.

BoomSmurf schreef op 22 januari 2004 @ 14:28:
[...]


U knaagt, wij aaien.

[loodgieter]

In ieder geval ook bedankte voor de genomen moeite. Het was de bedoeling om de klant die alle boeken heeft geleend te selecteren. Thanxxx

Verwijderd schreef op 22 januari 2004 @ 14:21:
Je vraagstelling is bovendien behoorlijk vaag.
Wil je een overzicht van de klant en alle boeken die hij heeft geleend?

In dat geval:

SELECT KlantGeg.Naam, BoekUitleen.ISBNnr
FROM KlantGeg INNER JOIN (KoppelTabel INNER JOIN BoekUitleen ON KoppelTabel.Plaatsnr = BoekUitleen.Plaatsnr) ON KlantReg.Sofinummer = KoppelTabel.Sofinummer
ORDER BY KlantGeg.Naam

Als je een lijst wilt hebben van alle klanten die een boek hebben geleend:

SELECT DISTINCT KlantGeg.Naam
FROM KlantGeg INNER JOIN (KoppelTabel INNER JOIN BoekUitleen ON KoppelTabel.Plaatsnr = BoekUitleen.Plaatsnr) ON KlantReg.Sofinummer = KoppelTabel.Sofinummer
ORDER BY KlantGeg.Naam

[BoomSmurf]

Twix schreef op 22 januari 2004 @ 14:36:
De query werkt perfect!! Alleen oorspronkelijk was de opdracht om 2x exist resp. not exist te gebruiken. Maar volgens mij is dit niet mogelijk.

Hang er een exist achter die altijd true geeft bijvoorbeeld:

code:

AND EXISTS(SELECT * FROM KLANTGEG)

Daar heb je je twee exists

Lever ze allebei in (al zou ik zeker proberen het zelf te verbeteren, want dit is 'top of my head' - waarschijnlijk is het met goeie joins makkelijker en mooier te doen, zie suggesties en queries van andere posters), krijg je misschien wel extra punten omdat je één exist gebruikt (of geen punten ).

[ Voor 2% gewijzigd door BoomSmurf op 22-01-2004 14:43 . Reden: spellen spellen spellen spellen spellen ]

[Maasluip]

Twix schreef op 22 januari 2004 @ 14:36:
De query werkt perfect!! Alleen oorspronkelijk was de opdracht om 2x exist resp. not exist te gebruiken. Maar volgens mij is dit niet mogelijk.


[...]

1. GoT is er niet voor je huiswerk
2. Dat had je er niet bij gezet
3. Gaan we op GoT nu ook al Outlook-style quoten? Is dat toegestaan zonder een Holy War te ontketenen?

[loodgieter]

Dit is inderdaad een goeie mogelijkheid. We zullen zelf eens even gaan kijken of we er in geheel een mooie query van kunnen bouwen. In ieder geval harstikke bedankt voor de moeite. !!

BoomSmurf schreef op 22 januari 2004 @ 14:43:
[...]


Hang er een exist achter die altijd true geeft bijvoorbeeld:

code:

AND EXISTS(SELECT * FROM KLANTGEG)

Daar heb je je twee exists

Lever ze allebei in (al zou ik zeker proberen het zelf te verbeteren, want dit is 'top of my head' - waarschijnlijk is het met goeie joins makkelijker en mooier te doen, zie suggesties en queries van andere posters), krijg je misschien wel extra punten omdat je één exist gebruikt (of geen punten ).

[curry684]

mdeen schreef op 22 januari 2004 @ 14:44:
[...]
1. GoT is er niet voor je huiswerk

Huiswerkvragen zijn reeds een half jaar of zo weer toegestaan, mits afdoende en duidelijk onderbouwd dat je zelf reeds al het mogelijke hebt gedaan.

2. Dat had je er niet bij gezet

Jawel hoor.

[Maasluip]

curry684 schreef op 22 januari 2004 @ 14:53:
[...]

Huiswerkvragen zijn reeds een half jaar of zo weer toegestaan, mits afdoende en duidelijk onderbouwd dat je zelf reeds al het mogelijke hebt gedaan.

Ah, wist ik niet (en had ook de FOK regeltjes in mijn achterhoofd)

[...]

Jawel hoor.

Ik was niet helemaal duidelijk denk ik. Ik refereerde naar de opmerking dat er twee exists in moesten zitten.

[loodgieter]

Nou allemaal bedankt

Uiteindelijk is het de volgende query geworden voor de belangstellenden :

Select klantgeg.naam
From klantgeg
Where
((select count(ISBNNR) from boeken) = (select count(plaatsnr) from boekuitleen)) and exists
(select count(distinct sofinummer) from koppeltabel)
and exists
(select distinct sofinummer from koppeltabel
where koppeltabel.sofinummer = klantgeg.sofinummer)

[BoomSmurf]

Twix schreef op 22 januari 2004 @ 16:04:
Nou allemaal bedankt

Uiteindelijk is het de volgende query geworden voor de belangstellenden :

Select klantgeg.naam
From klantgeg
Where
((select count(ISBNNR) from boeken) = (select count(plaatsnr) from boekuitleen)) and exists
(select count(distinct sofinummer) from koppeltabel)
and exists
(select distinct sofinummer from koppeltabel
where koppeltabel.sofinummer = klantgeg.sofinummer)

and exists
(select count(distinct sofinummer) from koppeltabel)

Dat is natuurlijk altijd waar, als er geen distincte sofinummers zijn krijg je een regel met 0 terug, dus ik ben bang dat dit niet goed gaat werken