sql / php

[elohmrow]

In a paractice exam, the question is:

select fname,lname
from student
where student_id in
(select sid
from registration
where iid in
(select instructor_id
from faculty
where fname="dumbledore"))

restate the query as a join operation in sql.


VERY new, would like to see how a pro does it. Knowing nothing about joins yet, here is my try:

select student.fname,student.lname,student.student_id
registration.sid,registration.iid,
faculty.instructor_id, faculty.fname
from student,
faculty join registration on
faculty.instructor_id = registration.iid
join student on
student.student_id = registration.sid
where faculty.fname="dumbledore"

please tell me what is wrong with this and how to fix it.

~elohmrow

[teknomage1]

Well as a general style thing, I'd say you should capitalize your sql commands to make them distinct, e.g. "SELECT field2 FROM table1 WHERE id = 7" versus "select field2 from table1 where id = 7" Also I'm pretty sure you don't need to explicitly call the join command to perform a join, rather it automatically joins when you select from multiple tables.
I think a proper join would be "SELECT student.name, student.grade, instrucor.name FROM student, instructor WHERE instructor.id = student.instructor_id"

[elohmrow]

Thank you for the reply.

The first snippet I provided is a join, but the practice exam wants instead of the join provided, for us to explicitly say "JOIN" in there. So the point is to convert what was given to an equivalent statement using explicit JOIN statements.

[sde]

fun, 3 tables

SELECT student.fname, student.lname from student
  LEFT JOIN registration on student.sid=registration.sid
  LEFT JOIN faculty on registration.iid=faculty.instructor_id
  WHERE faculty.fname='dumbledore'

[elohmrow]

much obliged, hombre

[teknomage1]

I don't understand the middle finger smiley...

[redhead]

We've had this discussion befor, some claim it to be a "thumbs up" smiley, others think it's a "flip you the bird" smiley...

[teknomage1]

Oh, I guess I can see that.

[sde]

lol, i forgot about it .. i always take it as people being a smart ass giving me the bird =)