i came accross requirement of multiple foreign keys in a table
referencing the same primary key another table. i created the table
relations using the relations editor in access. even though i could
have multiple foreign keys to a table access did allow the referential
integrity.

my database structure is

table1 - students table
table 2 - subjects table
table 3 - subjects opted by students

in the table 3 totally there are 6 subject columns and a student can
opt for variable number of subjects.

table structure

students
ID -> primary key, FirstName, LastName, ........

subjects
ID->primary key, subject

student_subjects
ID->primary key, StudentID->foreign key referencing students.ID, and
sub1, sub2, .....sub6 all of which reference the primary key in
subjects table.

now if in the student_subjects table i have a record with data
ID-1000, StudentID-100, sub1-1, sub2-2......sub6-6,

how can i join table in SQL query to access the student_subjects table
so that i can display the result as

ID->studentID (name not required)
but from sub1 to sub6 i should display the actual subject name that is
in the subjects table ?

for example :

ID sub1 sub2
sub3 ........................ sub6
1000 telecom. cryptography
networking ....................... cgi/perl

ravichoudhari (AT) gmail (DOT) com wrote:

table3(student_id*,subject_id*)

so SELECT * FROM table3 might look like this:

student1, subject2

student1, subject3

student2, subject1
student2, subject2
etc

<ravichoudhari (AT) gmail (DOT) com> wrote

Your table3 has been crosstabulated. As a consequence, it is not in first
normal form.
If it were in first normal form , the query would be a little easier. As
it is, you are going to have to do six outer joins between the
student_subjects table and the subjects table. The reason I say outer joins
is that I presume some students have less than six courses, with NULL in
the extra sub1..sub6 columns.

Perhaps a better design for the student_subjects table would be:

student_id (foreign key)
subject_id (foreign key)
sequence_no (varies from 1 to 6)

The sequence_no is not strictly necessary, but you could use it to indicate
where this subject would go in a crosstabulated view, if you wanted one.

This table design has several advantages over yours.

First, it is a whole lot easier to generate a student roster for each
subject.
Second, if you ever change to 7 courses per strduent, you won't invalidate
your existing structure.
There are other advantages.

I suggest you learn how to design relational tables in accordance with the
relational data model. Read Date's Introduction, or some other fine book
on the subject.