CODEWITHSUNDEEP
sqlselectsqliteforeign-keys
Andy M
Andy M

Reputation: 167

How to select sqlite result from multiple tables with multiple foreign keys

I want to select result in sqlite from multiple tables with multiple foreign keys, I tried JOIN but it did not work that well; for example :

a table for STUDENT :

 CREATE TABLE STUDENT (
 STUDENT_NAME    TEXT        NOT NULL,
 STUDENT_NUMBER  INTEGER  PRIMARY KEY  NOT NULL,
 STUDENT_ADDRESS TEXT        NOT NULL
 );

and a table for EXAMS :

 CREATE TABLE EXAMS(
 EXAM_CODE      INTEGER   PRIMARY KEY  NOT NULL,
 EXAM_SUBJECT   TEXT        NOT NULL,
 EXAM_LOCATION  TEXT        NOT NULL
 );

and a table called WROTE_EXAM to get the information for students who wrote a specific exam

 CREATE TABLE WROTE_EXAM (
 STUDENT_NUMBER  INTEGER     NOT NULL,
 EXAM_CODE       INTEGER     NOT NULL,
 DATE            DATE        NOT NULL,
 FOREIGN KEY(STUDENT_NUMBER) REFERENCES STUDENT(STUDENT_NUMBER),
 FOREIGN KEY(EXAM_CODE) REFERENCES EXAMS(EXAM_CODE));

this is a sample data inserted into tables : 

STUDENT_NAME : John
STUDENT_NUMBER: 123456789
STUDENT_ADDRESS : 10th street
EXAM_CODE: 123
EXAM_SUBJECT: One Subject
EXAM_LOCATION: Class

now, I want to :
a) output student names, exam codes and student location, who wrote the exam
b) output exam code, exam subject and exam location for student with ID : 123456789

thanks

Upvotes: 5

Views: 13850

Answers (1)

mu is too short
mu is too short

Reputation: 434665

When joining tables you almost always want to include an explicit join condition. The SQLite syntax diagrams may be helpful:

Join Op Join Constraint

So the SQL from your comment should look more like this:

select student.student_name, exams.exam_code, ...
from student
join wrote_exam using (student_number)
join exams using (exam_code)
where ...

or you could use JOIN...ON:

select student.student_name, exams.exam_code, ...
from student
join wrote_exam on student.student_number = wrote_exam.student_number
join exams on exams.exam_code = wrote_exam.exam_code
where ...

Then add where conditions to filter the results as needed.

Note that I've also added some table qualifiers to the columns in your SELECT clause, those are needed to uniquely specify which exam_code you're interested in and since one column needs to be qualified, I did them all for consistency; in real life I'd prefix them all to make things nice and explicit.

Also, I don't see a student_location anywhere, perhaps you mean student.student_address or exams.exam_location.

Upvotes: 11

Related Questions