left outer join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

left outer join

Author

Topic

g_p
Starting Member

48 Posts

Posted - 2006-12-04 : 10:26:01

Good evening!

Well,in relational algebra we have left outer join.
In SQL is any command for that?
How can i use left outer join?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-04 : 10:28:24

select * from customers c
left join orders o on o.customerid = c.customerid

this selects all customers and their orders (if they have any)

Peter Larsson
Helsingborg, Sweden

g_p
Starting Member

48 Posts

Posted - 2006-12-04 : 11:13:09

Well, in my example i have two tables

Student(iid , lname,course_number , teacher_id)
Teacher(tid, lname,course_number , s_number)

My query is : For every teacher return his lname and the lnames of every student who attend his lesson.

I do

select * Teacher.lname , Student.lname
from Student,Teacher
left join orders Teacher.tid = Student.teacher_id

but it doesn't work
What exactly do i do wrong?

Thanks,in advance!

X002548
Not Just a Number

15586 Posts

Posted - 2006-12-04 : 11:17:22

Where does Orders come from?



		 SELECT Teacher.lname, Student.lname
		   FROM Teacher
        LEFT OUTER JOIN Student 
		     ON Teacher.tid = Student.teacher_id

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-04 : 11:17:27

First of all, you are missing a comma after the asterisk.
Then you cross join (cartesian product) students and teachers. And then you LEFT JOIN the Orders table.

select *
from teacher t
left join student s on s.teacher_id = t.tid

But this table layout is not good for you.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-04 : 11:18:26

What is s_number in teacher table?
Did you not learn enough here at this topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=75624 ?

Peter Larsson
Helsingborg, Sweden

g_p
Starting Member

48 Posts

Posted - 2006-12-04 : 11:27:50

Thank you all!
I corrected my mistakes and now it works :)

quote:
Originally posted by Peso

What is s_number in teacher table?

Peter Larsson
Helsingborg, Sweden

I wanted to say d_number except for s_number where d_number is the department number

X002548
Not Just a Number

15586 Posts

Posted - 2006-12-04 : 11:38:01

quote:
Originally posted by g_p
I wanted to say d_number except for s_number where d_number is the department number

Hey, are you one of the people in HR Business dept that write the specs I ask for?

Sure sounds like it

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

g_p
Starting Member

48 Posts

Posted - 2006-12-04 : 12:00:02

quote:
Originally posted by X002548

quote:
Originally posted by g_p
I wanted to say d_number except for s_number where d_number is the department number

Hey, are you one of the people in HR Business dept that write the specs I ask for?

Sure sounds like it

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Hi,
Well i don't what HR business is,
I'm just doing a homework for the course i attend :)

g_p
Starting Member

48 Posts

Posted - 2006-12-04 : 12:00:35

I don't know

X002548
Not Just a Number

15586 Posts

Posted - 2006-12-04 : 12:03:52

HR as in Human resources for a major corporation

Home work, eh....

What's the course

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

g_p
Starting Member

48 Posts

Posted - 2006-12-04 : 12:06:55

Well, I'm having a problem again
I have made the file query.sql and when i try to do
SQL > @query
SQL >
i get nothing.
Before some minutes i had the desirable output and without changing anything in the file , it doesn't output anything now.

Do you know what's going wrong ?
Thanks, in advance!

g_p
Starting Member

48 Posts

Posted - 2006-12-04 : 12:09:40

quote:
Originally posted by X002548

HR as in Human resources for a major corporation

Home work, eh....

What's the course

Brett

8-)

A course in Europe :)

X002548
Not Just a Number

15586 Posts

Posted - 2006-12-04 : 12:10:14

quote:
Originally posted by g_p

Do you know what's going wrong ?

Yeah, it's homework

I don't even know what platform you re using

In any case, don't you have teachers you can go get help from?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-12-04 : 12:11:33

quote:
Originally posted by g_p

Well, I'm having a problem again
I have made the file query.sql and when i try to do
SQL > @query
SQL >
i get nothing.
Before some minutes i had the desirable output and without changing anything in the file , it doesn't output anything now.

Do you know what's going wrong ?
Thanks, in advance!

Based on the information you supplied: you did something wrong.

CODO ERGO SUM

g_p
Starting Member

48 Posts

Posted - 2006-12-04 : 12:15:21

quote:
In any case, don't you have teachers you can go get help from?

Yeah,I have asked the basic stuff but I'm a bit shy to occupy them again with many questions

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-12-04 : 12:20:09

quote:
Originally posted by g_p

quote:
In any case, don't you have teachers you can go get help from?

Yeah,I have asked the basic stuff but I'm a bit shy to occupy them again with many questions

That's what they're paid to do. Get what you're paying for.

CODO ERGO SUM

g_p
Starting Member

48 Posts

Posted - 2006-12-04 : 12:23:28

I have inside the query.sql the

select Student.lname
from Student

and it doesn't output anything

X002548
Not Just a Number

15586 Posts

Posted - 2006-12-04 : 12:43:47

OK, once more, what platform is this on?

I'm guessing Oracle

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

g_p
Starting Member

48 Posts

Posted - 2006-12-04 : 13:00:06

yeah it's oracle,
when i paste the code on the console (SQL> pasting...) it works
but when i do @query no output is printed :)

X002548
Not Just a Number

15586 Posts

Posted - 2006-12-04 : 13:08:39

Just to clarify...the @ sign used ij a command line oracle developer suite, is it's archane way of execute a script file...you need supply the full file name and the full path and then it will execute

quote:

Yeah,I have asked the basic stuff but I'm a bit shy to occupy them again with many questions

And I don't buy it...they probably told you to catch up on chapters you haven't read yet

And this is a Microsofty SQL Server Board....a few of us know Oracle, but you'd be better off, a). With reading the book and your teacher, or b). Here http://www.dbforums.com/forumdisplay.php?f=4, it's an Oracle site

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources