Re: How to find values without a FK?

From: Lew Pitcher <lew.pitcher_at_digitalfreehold.ca>
Date: Wed, 03 Apr 2019 15:36:08 -0400
Message-ID: <q831v8$58o$1_at_dont-email.me>

Lew Pitcher wrote:

> ^Bart wrote:
> 

>> I got two tables to manage school's exams:
>>
>> subjects
>> --------
>> id_subject
>> name
>>
>> users
>> -----
>> id_user
>> name
>> FK_id_subject
>>
>> The values of user's table are: "Tom, Peter, Sam"
>>
>> The values of subject's table are: "Economics, Biology, Latin, History,
>> Religion, German"
>>
>> An user like Tom is linked by a FK to subjects "Economics, Biology,
>> Latin" and it's easy to show which exams Tom passed but I need to show
>> also which exams Tom should do in the future ("History, Religion,
>> German"), how can I do it?

> 
[Quoted] > The easiest way I can think of is a many-to-many table.
> 
>   CREATE TABLE user_subject (
>     id_user  .... ,
>     id_subject .... ,
>     status ENUM ('complete', 'future'),
>     FOREIGN KEY id_user REFERENCES users(id_user),
>     FOREIGN KEY id_subject REFERENCES subjects(id_subject),
>     PRIMARY KEY (id_user, id_subject)
>   )

[snip]

DROP DATABASE school;

CREATE DATABASE school
  CHARACTER SET ascii;

USE school;

CREATE TABLE subjects (
  id_subject INTEGER UNSIGNED NOT NULL PRIMARY KEY,   name VARCHAR(15) NOT NULL
)
ENGINE InnoDB,
DEFAULT CHARACTER SET ascii;

CREATE TABLE users (
  id_user INTEGER UNSIGNED NOT NULL PRIMARY KEY,   name VARCHAR(65) NOT NULL
)
ENGINE InnoDB,
DEFAULT CHARACTER SET ascii;

CREATE TABLE user_subject (
  id_user INTEGER UNSIGNED NOT NULL,
  id_subject INTEGER UNSIGNED NOT NULL,

  status ENUM ('complete', 'future'),
  FOREIGN KEY (id_user) REFERENCES users(id_user),
  FOREIGN KEY (id_subject) REFERENCES subjects(id_subject),
  PRIMARY KEY (id_user, id_subject)

)
ENGINE InnoDB,
DEFAULT CHARACTER SET ascii;

• Populate course ("subjects") table with course names and numbers INSERT INTO subjects (id_subject, name) VALUES (1,"Civics"); INSERT INTO subjects (id_subject, name) VALUES (3,"Economics"); INSERT INTO subjects (id_subject, name) VALUES (5,"History"); INSERT INTO subjects (id_subject, name) VALUES (10,"Physics"); INSERT INTO subjects (id_subject, name) VALUES (11,"Biology"); INSERT INTO subjects (id_subject, name) VALUES (12,"Algebra"); INSERT INTO subjects (id_subject, name) VALUES (20,"German"); INSERT INTO subjects (id_subject, name) VALUES (21,"Latin"); INSERT INTO subjects (id_subject, name) VALUES (22,"Greek"); INSERT INTO subjects (id_subject, name) VALUES (30,"Religion"); INSERT INTO subjects (id_subject, name) VALUES (31,"Philosophy");
• Populate student ("users") table with student names and numbers INSERT INTO users (id_user, name) VALUES (97, "Bruno"); INSERT INTO users (id_user, name) VALUES (100, "Tom"); INSERT INTO users (id_user, name) VALUES (101, "Agatha");
• Populate user/subject table with tailored student curriculum INSERT INTO user_subject (id_user, id_subject, status) VALUES (100,3,"complete"); INSERT INTO user_subject (id_user, id_subject, status) VALUES (100,11,"complete"); INSERT INTO user_subject (id_user, id_subject, status) VALUES (100,21,"complete"); INSERT INTO user_subject (id_user, id_subject, status) VALUES (100,5,"future"); INSERT INTO user_subject (id_user, id_subject, status) VALUES (100,30,"future"); INSERT INTO user_subject (id_user, id_subject, status) VALUES (100,20,"future");

INSERT INTO user_subject (id_user, id_subject, status) VALUES (97,20,"future");
INSERT INTO user_subject (id_user, id_subject, status) VALUES (97,22,"complete");
INSERT INTO user_subject (id_user, id_subject, status) VALUES (97,10,"complete");
INSERT INTO user_subject (id_user, id_subject, status) VALUES (97,11,"future");

INSERT INTO user_subject (id_user, id_subject, status) VALUES (101,3,"complete");
INSERT INTO user_subject (id_user, id_subject, status) VALUES (101,22,"complete");
INSERT INTO user_subject (id_user, id_subject, status) VALUES (101,11,"future");
INSERT INTO user_subject (id_user, id_subject, status) VALUES (101,5,"future");

• 1) List completed subjects by student SELECT users.name, subjects.name FROM users, subjects, user_subject WHERE users.id_user = user_subject.id_user AND subjects.id_subject = user_subject.id_subject AND user_subject.status = "complete" ORDER BY users.name ASC, subjects.name ASC;
• 2) List all courses, and their status in the curriculum for student "Tom" SELECT subjects.name, user_subject.status FROM users, subjects, user_subject WHERE users.name = "Tom" AND user_subject.id_user = users.id_user AND subjects.id_subject = user_subject.id_subject ORDER BY user_subject.status ASC, subjects.name ASC;
• 3) List all students that have "Biology" in their curriculum SELECT users.name, user_subject.status FROM users, subjects, user_subject WHERE subjects.name = "Biology" AND user_subject.id_subject = subjects.id_subject AND users.id_user = user_subject.id_user ORDER BY users.name ASC;
• 4) List all students who have completed "Biology" SELECT users.name FROM users, subjects, user_subject WHERE subjects.name = "Biology" AND user_subject.id_subject = subjects.id_subject AND user_subject.status = "complete" AND users.id_user = user_subject.id_user ORDER BY users.name asc;
  1. List completed subjects by student name name Agatha Economics Agatha Greek Bruno Greek Bruno Physics Tom Biology Tom Economics Tom Latin
  2. List all courses, and their status in the curriculum for student "Tom" name status Biology complete Economics complete Latin complete German future History future Religion future
  3. List all students that have "Biology" in their curriculum name status Agatha future Bruno future Tom complete
  4. List all students who have completed "Biology" name Tom

-- 
Lew Pitcher
"In Skills, We Trust"

Received on Wed Apr 03 2019 - 21:36:08 CEST