[MYSQL] WHERE clause ná LEFT JOIN

[Verwijderd]

Ik ben bezig met een query die niet wil lukken. Het gaat om een systeem waar gebruikers een collega kunnen beoordelen aan de hand van een vragenlijst.
- De vragen staan in de tabel vragen
- De antwoorden worden opgeslagen in de tabel antwoorden
- De beoordeling wordt opgeslagen in de tabel beoordeling

De tabelstructuur is als volgt (versimpeld)

vragen	antwoorden	beoordelingen
vraag_id	vraag_id	beoordeling_id
vraag	beoordeling_id	beoordeelde
	antwoord	beoordelaar

Niet alle vragen zijn verplicht, dus het kan voorkomen dat een persoon meerdere keren beoordeeld wordt maar de ene beoordelaar vraag 2 niet invult en de andere wel.

Nu wil ik het volgende resultaat uit deze query

vraag	antwoord	beoordeelde
2	6	Henkie Penkie
2	NULL	Henkie Penkie
2	9	Henkie Penkie
2	NULL	Henkie Penkie

Ik wil dus ook de vragen te zien krijgen waar geen antwoord op is gegeven. Dit dacht ik te doen met onderstaande query, maar deze laat bovenstaand resultaat zien zonder de rijen waar vraag 2 niet is ingevuld.

code:

SELECT  * FROM antwoorden
RIGHT JOIN beoordeling ON antwoorden.beoordeling_id = beoordeling.beoordeling_id
LEFT JOIN vragen ON antwoorden.vraag_id = vragen.vraag_id
WHERE beoordeling.pnr_beoordeelde = 'Henkie Penkie'
AND vragen.vraag_id = 2

Hoe krijg ik het voor elkaar de niet ingevulde vragen toch in mijn resultset te krijgen? Is het überhaupt mogelijk met deze tabelstructuur. Ik heb het gevoel dat ik een denkfout maak.

[MTWZZ]

Je zult toch ergens moeten gaan vastleggen welke vragen er bij een beoordeling horen.
Tabelstructuur ongeveer zo:

BeoordelingDefinitie: Bevat een omschrijving van het soort beoordeling
Beoordeling: Bevat de beoordelingen die voor een bepaald persoon zijn gedaan
Vraag: Doet wat het zegt
BeoordelingDefinitieVragen: Geeft aan welke vragen er bij welke soort beoordeling horen
Antwoord: Bevat het antwoord op een vraag/beoordeling combinatie.

Je kunt nu bepalen welke vragen er bij welke beoordeling hoort:

SQL:

SELECT  *
FROM     BeoordelingDefinitie
INNER JOIN BeoordelingDefinitieVragen
ON BeoordelingDefinitieVragen.bd_id = BeoordelingDefinitie.bd_id
INNER JOIN Vragen
ON Vragen.vraag_id = BeoordelingDefinitieVragen.vraag_id
WHERE BeoordelingDefinitie.bd_id = 1

Als je nu wil uitvragen welke vragen nog niet beantwoord zijn kun je dit doen:

SQL:

SELECT  *
FROM     BeoordelingDefinitie
INNER JOIN BeoordelingDefinitieVragen
ON BeoordelingDefinitieVragen.bd_id = BeoordelingDefinitie.bd_id
INNER JOIN Vragen
ON Vragen.vraag_id = BeoordelingDefinitieVragen.vraag_id
LEFT JOIN Antwoorden
ON  Antwoorden.bdv_id = BeoordelingDefinitieVragen.bdv_id
WHERE BeoordelingDefinitie.bd_id = 1
AND Antwoorden.bdv_id IS NULL

[ Voor 19% gewijzigd door MTWZZ op 14-03-2011 19:51 ]

[Verwijderd]

Bedankt voor je uitgebreide antwoord. Ik snap alleen niet helemaal waar jouw beoordelingDefinitieVragen tabel in voorziet. Ik weet al welke vraag er bij welk antwoord hoort omdat het 'vraag_id' veld al in de tabel antwoord zit. Ook weet ik welke antwoorden er bij een beoordeling horen, omdat 'beoordeling_id' is opgenomen in de antwoord tabel.

[MTWZZ]

Stel je hebt een aantal beoordelingen: kwartaal, jaarlijks en 2-jaarlijks. Per beoordeling wil je een aantal vragen toekennen waaronder een paar die voor alle 3 gelden, bijvoorbeeld "heb je nog koffie".
Als je zo'n beoordeling verder samensteld heb je nog geen antwoorden en zou je in jouw situatie niet kunnen bepalen welke vragen er bij die beoordeling horen en dus ook niet welke vragen er nog niet beantwoord zijn.

[Verwijderd]

Oke, ik ben denk ik niet duidelijk geweest.

De tabel "beoordelingen" in mijn geval, bevat niet verschillende formulieren met vragen of soorten beoordelingen, maar bevat algemene informatie over de beoordeling (feedback) die een medewerker heeft gegeven. Hierin staan gegevens zoals de naam van de beoordelaar, de persoon die beoordeeld is, timestamp van wanneer de beoordeling gegeven is. Een beoordeling heeft een ID, welke weer terugkomt in de antwoorden tabel om zo te kunnen achterhalen welke antwoorden (op de vragen van het formulier) bij die beoordeling horen.

Vragen worden niet gekoppeld aan een beoordeling maar aan een formulier. Dit is in mijn database ook een tabel maar heb ik hier alleen niet genoemd.

[MTWZZ]

Verwijderd schreef op maandag 14 maart 2011 @ 20:46:
Vragen worden niet gekoppeld aan een beoordeling maar aan een formulier. Dit is in mijn database ook een tabel maar heb ik hier alleen niet genoemd.

Ok jouw formulier tabel is dan te vergelijken met de definitie tabel die ik beschreven heb.
Query zou dan wel toepasbaar moeten zijn.

[NMe]

Ik zou alvast beginnen met je query zo schrijven dat je maar één type outer joins gebruikt, dat leest makkelijker. Dus: beoordeling LEFT JOIN vraag LEFT JOIN antwoord. Volgens mij is je probleem daarmee ook al opgelost.

[Bolukan]

Zo beter:

SQL:

SELECT  * FROM antwoorden
RIGHT JOIN beoordeling ON antwoorden.beoordeling_id = beoordeling.beoordeling_id
LEFT JOIN vragen ON antwoorden.vraag_id = vragen.vraag_id
WHERE (beoordeling.pnr_beoordeelde = 'Henkie Penkie')
AND ((vragen.vraag_id = 2) OR (vragen.vraag_id Is Null))

[ Voor 191% gewijzigd door Bolukan op 15-03-2011 07:44 ]

[Verwijderd]

Ja, die laatste query heb ik ook geprobeerd maar geeft niet de rijen weer waar vraag 2 niet ingevuld is door een beoordelaar.

[freak-ish]

Ok, misschien klopt dit niet met MySQL maar kun je niet gewoon een OUTER join doen? Dan krijg je bijv.alles van tabel 1 en dus ook de Null van tabel 2.

[Bolukan]

Zou wel moeten werken <kijkt>1 minuut</kijkt> Zie het: WHERE moet op antwoorden staan en niet op vragen

SQL:

SELECT  * FROM antwoorden
RIGHT JOIN beoordeling ON antwoorden.beoordeling_id = beoordeling.beoordeling_id
LEFT JOIN vragen ON antwoorden.vraag_id = vragen.vraag_id
WHERE (beoordeling.pnr_beoordeelde = 'Henkie Penkie')
AND ((antwoorden.vraag_id = 2) OR (antwoorden.vraag_id Is Null))

[ Voor 6% gewijzigd door Bolukan op 15-03-2011 08:40 ]

[Verwijderd]

Ook dat maakt helaas geen verschil. De rijen waar vraag 2 niet is ingevuld worden niet weergegeven.

[NMe]

Verwijderd schreef op dinsdag 15 maart 2011 @ 09:22:
Ook dat maakt helaas geen verschil. De rijen waar vraag 2 niet is ingevuld worden niet weergegeven.

...en mijn suggestie heb je niet geprobeerd?

[Verwijderd]

Jawel, maar maakt geen verschil in het resultaat.

[NMe]

Mijn datastructuur, komt zo te zien overeen met de jouwe:

SQL:

CREATE TABLE IF NOT EXISTS `Antwoord` (
  `VraagId` int(11) NOT NULL,
  `BeoordelingId` int(11) NOT NULL,
  `AntwoordTekst` varchar(255) NOT NULL,
  PRIMARY KEY  (`VraagId`,`BeoordelingId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `Beoordeling` (
  `BeoordelingId` int(11) NOT NULL auto_increment,
  `Beoordeelde` varchar(50) NOT NULL,
  `Beoordelaar` varchar(50) NOT NULL,
  PRIMARY KEY  (`BeoordelingId`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `Vraag` (
  `VraagId` int(11) NOT NULL auto_increment,
  `VraagTekst` varchar(255) NOT NULL,
  PRIMARY KEY  (`VraagId`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

Mijn query:

SQL:

SELECT *
FROM `Beoordeling` AS `b`
JOIN `Vraag` AS `v`
LEFT JOIN `Antwoord` AS `a` ON `a`.`VraagId` = `v`.`VraagId`
AND `b`.`BeoordelingId` = `a`.`BeoordelingId`

Mijn resultaat:

BeoordelingId	Beoordeelde	Beoordelaar	VraagId	VraagTekst	VraagId	BeoordelingId	AntwoordTekst
1	Henkie Penkie	Pietje Puk	1	Of je worst lust?	1	1	Ja, graag.
1	Henkie Penkie	Pietje Puk	2	Kun je antwoord geven op deze vraag?	NULL	NULL	NULL

Da's wat je wilde, toch?

[Verwijderd]

en plak er nu eens een

SQL:

 WHERE `b`.`Beoordelaar` = 'Pietje Puk'

achter, krijg je nou nog steeds de null values?

[ Voor 5% gewijzigd door Verwijderd op 15-03-2011 15:38 ]

[NMe]

Natuurlijk, want dat veld bevat gewoon 'Pietje Puk' in beide results? Heb je hem zelf al eens geprobeerd?

[Verwijderd]

Hmm dat werkt inderdaad! Bedankt daarvoor. Alleen snap nog niet helemaal waar de crux nou zit. Waarom krijg ik op mijn manier wel de antwoorden te zien maar niet de vragen die niet zijn ingevuld.

Waarom krijg ik met onderstaande query een ander resultaat:

SQL:

SELECT * FROM beoordeling
LEFT JOIN antwoorden ON beoordeling.beoordeling_id  = antwoorden.beoordeling_id
LEFT JOIN vragen ON vragen.vraag_id = antwoorden.vraag_id
WHERE beoordeling.pnr_beoordeelde = 223360
AND vragen.vraag_id = 2

[ Voor 5% gewijzigd door Verwijderd op 15-03-2011 17:06 ]

[NMe]

Omdat ik een join zonder criterium doe op de vragen om zo te zorgen dat ze allemaal in je result set zitten voor de volgende join.

Wat jij wil is alle beoordelingen linken aan alle vragen, oftewel een cartesisch product. Vervolgens wil je aan dát resultaat nog de antwoorden gaan linken. Dat cartesisch product vertaalt zich in een join zonder ON clause, die link met de antwoorden vertaalt zich in een left join, in dit geval met twee verschillende criteria in de ON clause om te zorgen dat je de juiste resultaten uitfiltert.

[Bolukan]

Omdat je vragen.vraag_id gebruikt ipv antwoorden.vraag_id ! (?)

[ Voor 5% gewijzigd door Bolukan op 16-03-2011 09:29 ]

[Verwijderd]

Bolukan schreef op woensdag 16 maart 2011 @ 09:28:
Omdat je vragen.vraag_id gebruikt ipv antwoorden.vraag_id ! (?)

Nee, dat maakt geen verschil. Je moet in dit geval echt een cross join gebruiken om de niet ingevulde vragen zichtbaar te krijgen (Zoals in het voorbeeld van NMe).