Re: SQL Newbie wants to select data from 3 tables

antonyliu2002_at_yahoo.com wrote:
> I am very very new to SQL. I want to get student information from 3
> tables T1, T2, T3 in an Oracle database.
>
> I tried this:
>
> select T1.ID, T1.Fname, T1.Lname, T2.StudentType, T2.StudentMajor,
> T3.PreviousCollege
> from T1, T2, T3
> where T1.ID = 123 and T2.ID=123 and T3.ID=123
>
> This works fine if ID 123 has a record in each of the three tables.
>
> I get 0 row if student ID 123 has records in both T1 and T2, but does
> not have a record in T3. In other words, if student ID 123 has not
> attended any previous college.
>
> I want to get one row of info about this student ID even if he/she does
> not have any record in T3. That is, in this case, I want to get things
> like below:
>
> Fname Lname StudentType StudentMajor PreviousCollege
> John Doe Freshman Accounting
>
> Why doesn't my sql do this for me?

You need to learn the syntax for "outer join," as well as for joins in general. To join, you need to tell sql what to join:

t1.id='123' and t1.id=t2.id and t1.id=t3.id.

Outer join means give rows in the other tables even if they don't exist in one. Go to tahiti.oracle.com and search for outer join. Here's a hint for you: SQL reference manual...

jg

--
@home.com is bogus.
"There will be a major event on the San Andreas Fault in Southern
California next week, plus or minus 100 years." - Geologist on radio,
interviewer seemed to miss the joke.