Dave Tolls wrote:tutor_subject only needs to have the tutor_id and the subject_id.
Why not? You autoincrement the ID in the subject table, and use it as if a foreign key in the teaches table.
tangara goh wrote:. . . The thing is that I can't really use subj_id cos it will auto-increment.
Please explain what you are really doing. If you show us something different from what you are actually doing, you are liable to get misleading advice. Look at this FAQ.
. . . it doesn't appear like what I had shown in my diagram.
So tutor 1 teaches subject 3 and sibject 4, and tutor 2 teaches subject 4 and subject 5? Yes, you can, but I have forgotten how to do it.
I'd like to know if it is possible to have insertion shown in my diagram ?
Isn't that what you want, so you can insert a tutor_ID and a subject_ID into the teaches table?
. . . the subj_Id will appear with the subjectName side by side
Dave Tolls wrote:This is where JOINs come into your queries.
Using the 'teaches' table (taking Campbell's name here), if you wanted all the subjects taught by a particular tutor then the following query would work:
FROM subject s JOIN teaches t ON s.id = t.sub_id
WHERE t.tut_id = ?;
then you would bind the tutor id to that parameter.
That would then give you a row for each subject that tutor teaches.
If you want to assign a subject to a tutor then:
INSERT INTO teaches
Dave Tolls wrote:Don't look at the table as just "finding subjects taught by a tutor"...it works the other way as well.
That's the nature of many-to-many.
For the ids generated, Statement (and so PreparedStatement) provide a getGeneratedKeys method, which returns a ResultSet of the ids generated from the last operation.
So, you would create the tutor (INSERT INTO tutor etc) and, after the executeUpdate you can then get the id:
(that second line might be slightly wrong, but it should be the first column).