Multiple joins (SQL language in Access database, in VB6)

[jurkic]

Hello,


i'm having some difficulties which You'll solve easily, i guess, so please help me:

I have 3 tables with this structure

1. PROFESOR
id
name
id_subject (that equals FIELD "id" in SUBJECT table)

2. SUBJECT
id
name

3. GRADES
id
id_profesor (equals FIELD "id" in PROFESOR table)
id_subject (equals FIELD "id" in SUBJECT table)
grade


I'm trying to display grades, with subject names and profesor names.

I can display these fields:
"GRADES.grade,SUBJECT.name"
with this SQL query

SELECT grades.grade, subject.name FROM subject INNER JOIN grades ON grades.id_subject=subject.id WHERE etc etc

Equally for "GRADES.grade,PROFESOR.name", but i cannot combine these three tables.

Hope to be answered :-)

Thank You in advance.

Matej

[NeoPa]

I don't think you need the id_subject (equals FIELD "id" in SUBJECT table) field in GRADES. It is redundant as the subject can be found from the Profesor link.
Anyway, try :

SELECT P.Name, S.Name, G.Grade
FROM (Grades AS G INNER JOIN Profesor AS P
  ON G.ID_Profesor=P.ID) INNER JOIN Subject AS S
  ON P.ID_Subject=S.ID

Notice the Grades.ID_Subject field is not required.

[jurkic]

I don't think you need the id_subject (equals FIELD "id" in SUBJECT table) field in GRADES. It is redundant as the subject can be found from the Profesor link.
Anyway, try :

SELECT P.Name, S.Name, G.Grade
FROM (Grades AS G INNER JOIN Profesor AS P
  ON G.ID_Profesor=P.ID) INNER JOIN Subject AS S
  ON P.ID_Subject=S.ID

Notice the Grades.ID_Subject field is not required.

Thank You very much for Your answer!

I agree, Grades.ID_Subject is not required at this time, because one profesor teaches just one subject, but it will be possible that one profesore teaches two or more subjects, for example, chemistry, phisycs and biology, so i'll need that identifier.

You've been very helpfull!

Matej

[NeoPa]

In that case you should remove ID_Subject from the Profesor table. It doesn't makes sense if you can have multiple subjects for a professor ;)
I'm pleased to have helped anyway.