JOIN TABLES...help!

hi,

this are my tables...

studentstud_numpk2pk3stud_group123411 22 1214788 66 2222222 22 2543255 44 1987699 77 1

student1stud_numpk2pk3agegradepass123411 22 21770543255 44 23901987699 77 23901

student2　　　　　stud_numpk2pk3agegradepass214788 66 18780222222 22 15901

and this is what I want to retrieve from these 3 tables

stud_numpk2pk3age543255 44 23987699 77 23222222 22 15

this my query...

SELECT student.stud_num, student.pk2, student.pk3, student1.age,
student2.age AS Expr1
FROM student INNER JOIN
student1 ON student.stud_num = student1.stud_num AND
student.pk2 = student1.pk2 AND student.pk3 = student1.pk3 INNER JOIN
student2 ON student.stud_num = student2.stud_num AND
student.pk2 = student2.pk2 AND student.pk3 = student2.pk3
WHERE (student1.grade = '90') AND (student1.pass = '1') AND (student2.grade = '90')
AND (student2.pass = '1')

however...it doesnt return any results...

can anyone correct my code?

thanx!

Sheila

Hi,

try this one:

SELECT student.stud_num, student.pk2, student.pk3, student1.age AS Expr1
FROM student INNER JOIN
student1 ON student.stud_num = student1.stud_num AND
student.pk2 = student1.pk2 AND student.pk3 = student1.pk3
WHERE (student1.grade = '90') AND (student1.pass = '1')

union

SELECT student.stud_num, student.pk2, student.pk3, student2.age AS Expr1
FROM student INNER JOIN
student2 ON student.stud_num = student2.stud_num AND
student.pk2 = student2.pk2 AND student.pk3 = student2.pk3
WHERE (student2.grade = '90') AND (student2.pass = '1')

Cheers,

Yani

|||

Since you don't actually require any of the data from the student table this would be simplier:

SELECT stud_num,pk2,pk3,age FROM student1 WHERE grade='90' AND pass='1'

UNION

SELECT stud_num,pk2,pk3,age FROM student2 WHERE grade='90' AND pass='1'

Alternatively, you could do this:

SELECT stud_num,pk2,pk3,age

FROM (

SELECT stud_num,pk2,pk3,age,grade,pass FROM student1

UNION

SELECT stud_num,pk2,pk3,age,grade,pass FROM student2

) t1

WHERE grade='90' AND pass='1'

|||

hi,

What if I wanted to get the names of the students . This is the additional table...

namesstud_numname1234Sheila2147Mikot2222Piwi5432Lorraine9876Grace

and this is now what I wanted to retrieve...

stud_numnamepk2pk3age5432Lorraine55 44 239876Grace99 77 232222Piwi22 22 15

What will be the code for the new query?

Thanks...

Sheila

|||

Hi,

It has been a long time since i did SQL query. Not sure if it is correct.

SELECT t1.stud_num,t1.pk2,t1.pk3,t1.age,names.name

FROM (

SELECT stud_num,pk2,pk3,age,grade,pass FROM student1

UNION

SELECT stud_num,pk2,pk3,age,grade,pass FROM student2

) t1, names

WHERE t1.grade='90' AND t1.pass='1' AND names.stud_num=t1.stud_num

|||

Hi,

if you are going to use the first approach with the multiple inner joins your query would look as :

SELECT student.stud_num, names.name, student.pk2, student.pk3, student1.age AS Expr1
FROM student INNER JOIN
student1 ON student.stud_num = student1.stud_num AND
student.pk2 = student1.pk2 AND student.pk3 = student1.pk3

INNER JOIN names on student.stud_num = names.stud_num
WHERE (student1.grade = '90') AND (student1.pass = '1')

union

SELECT student.stud_num, names.name, student.pk2, student.pk3, student2.age AS Expr1
FROM student INNER JOIN
student2 ON student.stud_num = student2.stud_num AND
student.pk2 = student2.pk2 AND student.pk3 = student2.pk3

INNER JOIN names on student.stud_num = names.stud_num
WHERE (student2.grade = '90') AND (student2.pass = '1')

Happy new year,

Yani