By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,563 Members | 905 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,563 IT Pros & Developers. It's quick & easy.

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

P: 2
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

Expand|Select|Wrap|Line Numbers
  1. 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
May 23 '07 #1
Share this Question
Share on Google+
3 Replies

NeoPa
Expert Mod 15k+
P: 31,770
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT P.Name, S.Name, G.Grade
  2. FROM (Grades AS G INNER JOIN Profesor AS P
  3.   ON G.ID_Profesor=P.ID) INNER JOIN Subject AS S
  4.   ON P.ID_Subject=S.ID
Notice the Grades.ID_Subject field is not required.
May 24 '07 #2

P: 2
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT P.Name, S.Name, G.Grade
  2. FROM (Grades AS G INNER JOIN Profesor AS P
  3.   ON G.ID_Profesor=P.ID) INNER JOIN Subject AS S
  4.   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
Jun 4 '07 #3

NeoPa
Expert Mod 15k+
P: 31,770
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.
Jun 4 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.