Results 1 to 11 of 11

Thread: Many-to-many SQL statement met null values in koppeltabel

  1. #1

    Many-to-many SQL statement met null values in koppeltabel

    Hallo hallo,

    Op SQL Fiddle heb ik de volgende (Postges 9.3) database aangemaakt met de volgende tabellen:

    Code:
    CREATE TABLE "Student" (
    	"studentID" int4 NOT NULL,
    	"naam" varchar(20),
    	PRIMARY KEY("studentID")
    );
    
    CREATE TABLE "StudentVak" (
    	"studentID" int4 NOT NULL,
    	"vakID" int4 NOT NULL,
    	PRIMARY KEY("studentID","vakID")
    );
    
    CREATE TABLE "Vak" (
    	"vakID" int4 NOT NULL,
    	"naam" varchar(20),
    	PRIMARY KEY("vakID")
    );
    
    
    ALTER TABLE "StudentVak" ADD CONSTRAINT "Ref_StudentVak_to_Student" FOREIGN KEY ("studentID")
    	REFERENCES "Student"("studentID")
    	MATCH SIMPLE
    	ON DELETE NO ACTION
    	ON UPDATE NO ACTION
    	NOT DEFERRABLE;
    
    ALTER TABLE "StudentVak" ADD CONSTRAINT "Ref_StudentVak_to_Vak" FOREIGN KEY ("vakID")
    	REFERENCES "Vak"("vakID")
    	MATCH SIMPLE
    	ON DELETE NO ACTION
    	ON UPDATE NO ACTION
    	NOT DEFERRABLE;
    
    INSERT INTO "Student"  VALUES (1,'Jan');
    INSERT INTO "Student"  VALUES (2,'Piet');
    INSERT INTO "Vak"  VALUES (1,'Engels');
    INSERT INTO "Vak"  VALUES (2,'Duits');
    INSERT INTO "Vak"  VALUES (3,'Frans');
    INSERT INTO "StudentVak" VALUES (1,1)
    Nu wil ik graag als result alle vakken die een bepaalde student volgt + alle vakken die de student niet volgt. In het geval van Jan wil ik als resultaat:

    Code:
    SV.studentID SV.vak V.vakID V.naam
    1 1 1 Engels
    null null 2 Duits
    null null 3 Frans
    Iemand een idee hoe ik dit oplos?

    Bij voorbaat dank!
    Last edited by luigi; 02-Feb-17 at 12:48.

  2. #2
    Als het alleen voor Jan is, dan is het niet Many to Many. Dan ben je er al met een left join. Sterker, volgens mij is dat sowieso wat je wilt hebben, tenzij je in dezelfde query ook de studenten wilt zien die helemaal geen vak volgen.

    SQL Code:
    1. SELECT
    2.   SV.studentId,
    3.   S.naam
    4.   SV.vakId, -- Waarom zou je dit nodig hebben?
    5.   V.VakId,
    6.   V.Naam
    7. FROM Vak V
    8. LEFT JOIN StudentVak SV ON SV.vakId = V.vakId
    9. LEFT JOIN Student S ON S.studentId = SV.studentId
    Last edited by GolezTrol; 02-Feb-17 at 14:40.
    1+1=b

  3. #3
    Zoiets ??

    SQL Code:
    1. SELECT *
    2. FROM Student s
    3. LEFT JOIN Vak v ON 1=1
    4. LEFT JOIN StudentVak sv ON sv.vakID=v.vakID AND sv.studentID=s.studentID
    Resultaat bijvoorbeeld:
    Code:
      studentid	naam	vakid	naam	studentid	vakid
    1	1	Jan	1	Engels	1	1
    2	1	Jan	2	Duits	NULL	NULL
    3	1	Jan	3	Frans	NULL	NULL
    4	2	Piet	1	Engels	NULL	NULL
    5	2	Piet	2	Duits	2	2
    6	2	Piet	3	Frans	2	3

    Een werkend voorbeeld kun je hier vinden
    Ik haat de tabellen met " eromheen dus heb ze hier even weggehaald.
    http://rextester.com/COYOT78642

    SQL Code:
    1. CREATE TABLE Student (
    2.     studentID int4 NOT NULL,
    3.     naam varchar(20),
    4.     PRIMARY KEY(studentID)
    5. );
    6.  
    7. CREATE TABLE StudentVak (
    8.     studentID int4 NOT NULL,
    9.     vakID int4 NOT NULL,
    10.     PRIMARY KEY(studentID,vakID)
    11. );
    12.  
    13. CREATE TABLE Vak (
    14.     vakID int4 NOT NULL,
    15.     naam varchar(20),
    16.     PRIMARY KEY(vakID)
    17. );
    18.  
    19.  
    20. ALTER TABLE StudentVak ADD CONSTRAINT Ref_StudentVak_to_Student FOREIGN KEY (studentID)
    21.     REFERENCES Student(studentID)
    22.     MATCH SIMPLE
    23.     ON DELETE NO ACTION
    24.     ON UPDATE NO ACTION
    25.     NOT DEFERRABLE;
    26.  
    27. ALTER TABLE StudentVak ADD CONSTRAINT Ref_StudentVak_to_Vak FOREIGN KEY (vakID)
    28.     REFERENCES Vak(vakID)
    29.     MATCH SIMPLE
    30.     ON DELETE NO ACTION
    31.     ON UPDATE NO ACTION
    32.     NOT DEFERRABLE;
    33.  
    34. INSERT INTO Student  VALUES (1,'Jan');
    35. INSERT INTO Student  VALUES (2,'Piet');
    36. INSERT INTO Vak  VALUES (1,'Engels');
    37. INSERT INTO Vak  VALUES (2,'Duits');
    38. INSERT INTO Vak  VALUES (3,'Frans');
    39. INSERT INTO StudentVak VALUES (1,1);
    40. INSERT INTO StudentVak VALUES (2,2);
    41. INSERT INTO StudentVak VALUES (2,3);
    42.  
    43. SELECT *
    44. FROM Student s
    45. LEFT JOIN Vak v ON 1=1
    46. LEFT JOIN StudentVak sv ON sv.vakID=v.vakID AND sv.studentID=s.studentID

  4. #4
    Quote Originally Posted by GolezTrol View Post
    SQL Code:
    1. SELECT
    2.   SV.studentId,
    3.   S.naam
    4.   SV.vakId, -- Waarom zou je dit nodig hebben?
    5.   V.VakId,
    6.   V.Naam
    7. FROM Vak V
    8. LEFT JOIN StudentVak SV ON SV.vakId = V.vakId
    9. LEFT JOIN Student S ON S.studentId = SV.studentId
    Het nadeel van deze query is dat je de NULL's dus niet krijgt
    (welke talen jan niet volgt)

    Probeer het maar eens uit door de statement hier in te vullen onderin:
    http://rextester.com/COYOT78642

    Dan krijg je:
    Code:
      studentid	naam	vakid	vakid	naam
    1	1	Jan	1	1	Engels
    2	2	Piet	2	2	Duits
    3	2	Piet	3	3	Frans
    En dan weet je nog niet de talen die Jan niet volgt.

    Ik denk dat er echt een ON 1=1 nodig is om die NULL regels te genereren.

  5. #5
    Ah, ik begreep de spec andersom, maar ik zat inderdaad niet scherp te wezen.
    1+1=b

  6. #6
    De oplossing van RVK is inderdaad wat ik zocht.

    Die 1=1 constructie kende ik niet weer wat geleerd.

    Bedankt! Ook de mindere scherpe onder ons

  7. #7
    Je kunt i.p.v. 1=1 ook TRUE gebruiken (werkt wel bij PostgreSQL maar niet bij alle andere DBMS).
    (Firebird kent bijvoorbeeld geen ON TRUE dus vandaar dat mijn eerste instinct 1=1 was)

    In principe zorgt dit er dus voor dat je voor alle studenten en alle vakken gecombineerd een regel hebt.
    SQL Code:
    1. SELECT *
    2. FROM Student s
    3. LEFT JOIN Vak v ON true
    Code:
      studentid	naam	vakid	naam
    1	1	Jan	1	Engels
    2	1	Jan	2	Duits
    3	1	Jan	3	Frans
    4	2	Piet	1	Engels
    5	2	Piet	2	Duits
    6	2	Piet	3	Frans

    Met de left join op StudentVak erbij heb je dus je gewenste resultaat.
    SQL Code:
    1. SELECT s.naam AS Student, v.naam AS Vak, CASE WHEN sv.studentId IS NULL THEN 'Nee' ELSE 'Ja' END AS Volgt
    2. FROM Student s
    3. LEFT JOIN Vak v ON true
    4. LEFT JOIN StudentVak sv ON sv.vakID=v.vakID AND sv.studentID=s.studentID
    Code:
      	student	vak	volgen
    1	Jan	Engels	Ja
    2	Jan	Duits	Nee
    3	Jan	Frans	Nee
    4	Jan	Spaans	Nee
    5	Piet	Engels	Nee
    6	Piet	Duits	Ja
    7	Piet	Frans	Ja
    8	Piet	Spaans	Nee

  8. #8
    Registered User TOPX's Avatar
    Join Date
    Apr 2016
    Location
    Bogotá
    Posts
    7
    Hallo,

    Dat 1 = 1 en ON TRUE conditie is heel slim, dankzij @rvk.

    Gezien het feit dat ik niet het bestaan ervan wist, zou ik hebben gebruik gemaakt van een cartesisch product query, met alle mogelijke "Vak" en alle mogelijke "Student". En dan gebruik maken van de beroemde LEFT JOIN.

    Net als het volgende ~

    SQL Code:
    1. SELECT geevenaard.*
    2. FROM (
    3.   SELECT
    4.     Student.studentID, Student.naam,
    5.     Vak.vakID, Vak.naam
    6.   FROM Student, Vak
    7. ) AS geevenaard
    8. LEFT JOIN StudentVak
    9.   ON StudentVak.studentID = geevenaard.studentID
    10.   AND StudentVak.vakID = geevenaard.vakID
    11. WHERE StudentVak.studentID IS NULL

    Je zou moeten doen bij de beoordeling van de prestaties van deze vraag tegen je eigen indexen.
    -
    ... dank u voor het verdragen van mij met mijn arme Nederlanders.

  9. #9
    Quote Originally Posted by TOPX View Post
    Net als het volgende ~

    SQL Code:
    1. SELECT geevenaard.*
    2. FROM (
    3.   SELECT
    4.     Student.studentID, Student.naam,
    5.     Vak.vakID, Vak.naam
    6.   FROM Student, Vak
    7. ) AS geevenaard
    8. LEFT JOIN StudentVak
    9.   ON StudentVak.studentID = geevenaard.studentID
    10.   AND StudentVak.vakID = geevenaard.vakID
    11. WHERE StudentVak.studentID IS NULL

    Je zou moeten doen bij de beoordeling van de prestaties van deze vraag tegen je eigen indexen.
    -
    Niet helemaal exact wat er gevraagd werd
    Het geeft alle talen die Jan en Piet NIET volgen. Maar niet in één tabel welke ze wel en niet volgen. (en dat was de vraag)

    Jouw resultaat met:
    http://rextester.com/XFEGIE88946
    Code:
     studentid	naam	vakid	naam
    1	1	Jan	2	Duits
    2	1	Jan	3	Frans
    3	2	Piet	1	Engels
    Maar met een hele kleine aanpassing klopt hij toch
    (de WHERE moet dus weg en je moet StudentVak.studentID opnemen in je kolommen)
    SQL Code:
    1. SELECT geevenaard.*, StudentVak.studentID
    2. FROM (
    3.   SELECT
    4.     Student.studentID, Student.naam,
    5.     Vak.vakID, Vak.naam
    6.   FROM Student, Vak
    7. ) AS geevenaard
    8. LEFT JOIN StudentVak
    9.   ON StudentVak.studentID = geevenaard.studentID
    10.   AND StudentVak.vakID = geevenaard.vakID
    Resultaat:
    Code:
      studentid	naam	vakid	naam	studentid
    1	1	Jan	1	Engels	1
    2	1	Jan	2	Duits	NULL
    3	1	Jan	3	Frans	NULL
    4	2	Piet	1	Engels	NULL
    5	2	Piet	2	Duits	2
    6	2	Piet	3	Frans	2
    Dan zie je wel alles en zie je ook of de vakken gevolgd worden.

  10. #10
    Wat is eigenlijk de functie van haakjes om een tabelnaam? Had dat iets met sql injection te maken? In mijn database designer stond het standaard aan, dus ben ik het blijven gebruiken, maar eigenlijk weet ik niet eens meer waarom.

  11. #11
    Nee, de "haakjes" hebben te maken met speciale tekens en gereserveerde woorden in tabel en veldnamen.
    http://stackoverflow.com/questions/2...e-column-names

    Maar ik zorg er gewoon altijd voor dat mijn tabel en veldnamen ze niet nodig hebben.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •