So I've been working on a Java program to query a database using SQL. While the majority of it is set up, I'm having some trouble formulating some of the queries.
For example, one query is meant to every unique identifier, 'ID', from one table, and then return the set of exams they attend, from a separate table which uses 'ID' as a foreign key. I'm not really sure how to form this as a query and I'm really stumped. Any help would be really appreciated.
Thanks
SELECT s.s_ID, e.ID, e.Exam_code, e.Exam_year, e.Score FROM student s JOIN exam e ON s.s_ID = e.s_ID WHERE s.s_ID = 1
Not enough rep., can't comment... )-;
Have you tried a JOIN operation?
For example, if your exams table has a row for each participant in each exam,
SELECT * FROM exams JOIN students ON exams.participant_student_id = students.id'
As a corollary,
SELECT * FROM exams JOIN students ON exams.participant_student_id = students.id where student.id = 123;
will get you a sub-set of the exams table for the student with ID = 123.
You should think about the best way to store your data in the database. I would suggest that a row per participant, per exam is reasonable for the structure of the exams table. This gives you more flexibility in future queries. The downside is that you will have to loop over the rows in your result set if you want the output to be a Set (or String).
SELECT * FROM TABLE1 AS t1 JOIN TABLE2 AS t2 ON t2.id = t1.id
where t1 is the table with id as foreign key
