Re: How to find values without a FK?
Date: 3 Apr 2019 18:25:28 GMT
Message-ID: <exams-20190403192207_at_ram.dialup.fu-berlin.de>
^Bart <gabriele1NOSPAM_at_hotmail.com> writes:
>which exams Tom should do in the future ("History, Religion,
Warning: Script will drop Schema S20190403192324!
DROP SCHEMA S20190403192324; CREATE SCHEMA S20190403192324; USE S20190403192324; CREATE TABLE PERSON( PERSON INT PRIMARY KEY, NAME VARCHAR ( 999 )); INSERT INTO PERSON( PERSON, NAME )VALUES( 0, 'Tom' );
CREATE TABLE SUBJECT( SUBJECT INT PRIMARY KEY, NAME VARCHAR ( 999 ) );
INSERT INTO SUBJECT( SUBJECT, NAME )
VALUES( 0, 'Economics' ),( 1, 'Biology' ),( 2, 'Latin' ),
( 3, 'History' ),( 4, 'Religion' ),( 5, 'German' );
CREATE TABLE PASSED
( PERSON INT, SUBJECT INT,
FOREIGN KEY ( PERSON ) REFERENCES PERSON ( PERSON ),
FOREIGN KEY ( SUBJECT ) REFERENCES SUBJECT ( SUBJECT ),
PRIMARY KEY( PERSON, SUBJECT ) );
INSERT INTO PASSED( PERSON, SUBJECT )VALUES
( 0, 0 ),( 0, 1 ),( 0, 2 );
SELECT SUBJECT.NAME FROM
( SELECT * FROM PASSED
INNER JOIN PERSON USING( PERSON )
WHERE PERSON.NAME = 'TOM' ) AS TOM
[Quoted] RIGHT JOIN SUBJECT USING( SUBJECT ) WHERE PERSON IS NULL;
Received on Wed Apr 03 2019 - 20:25:28 CEST