Inner Join question

[kieran]

Hi,

I have the following sql statement. I originally had the statement
with two INNER JOINS but in some situations was getting an error so
changed the last INNER JOIN to a LEFT OUTER JOIN (as is seem below).
This seemed to work but i am unsure why and would like to know in case
it falls over again. Why did the two INNER JOINS not work, and am I
correct to use the LEFT OUTER JOIN in this context.
As you can see the table 'tblStaff1_2' does not exist so I used the
line 'tblStaff tblStaff1_2' to create it temporarily. Does this have
something to do with it.

Thanks for all help.

---------------------

SELECT tblStaff.StaffNo, tblStaff.StandIn, tblStaff.FirstName AS
FirstName1, tblStaff.LastName AS LastName1,
tblDepartment.Dept, tblStaff.Telephone,
tblStaff.WorkTelephone, tblStaff1_2.FirstName AS FirstName2,
tblStaff1_2.LastName AS LastName2
FROM tblStaff INNER JOIN
tblDepartment ON tblStaff.DeptId =
tblDepartment.DeptId LEFT OUTER JOIN
tblStaff tblStaff1_2 ON tblStaff.StandIn =
tblStaff1_2.StaffNo
WHERE (tblStaff.NTUser = @NTUser)

--------------------------

[Simon Hayes]

ki********@hotmail.com (kieran) wrote in message news:<b3**************************@posting.google. com>...

Hi,

I have the following sql statement. I originally had the statement
with two INNER JOINS but in some situations was getting an error so
changed the last INNER JOIN to a LEFT OUTER JOIN (as is seem below).
This seemed to work but i am unsure why and would like to know in case
it falls over again. Why did the two INNER JOINS not work, and am I
correct to use the LEFT OUTER JOIN in this context.
As you can see the table 'tblStaff1_2' does not exist so I used the
line 'tblStaff tblStaff1_2' to create it temporarily. Does this have
something to do with it.

Thanks for all help.

---------------------

SELECT tblStaff.StaffNo, tblStaff.StandIn, tblStaff.FirstName AS
FirstName1, tblStaff.LastName AS LastName1,
tblDepartment.Dept, tblStaff.Telephone,
tblStaff.WorkTelephone, tblStaff1_2.FirstName AS FirstName2,
tblStaff1_2.LastName AS LastName2
FROM tblStaff INNER JOIN
tblDepartment ON tblStaff.DeptId =
tblDepartment.DeptId LEFT OUTER JOIN
tblStaff tblStaff1_2 ON tblStaff.StandIn =
tblStaff1_2.StaffNo
WHERE (tblStaff.NTUser = @NTUser)

--------------------------

You need to provide some more information - what was the error message
and what do you mean by "in some situations" and "did not work"?

To get a good answer to a SQL query question, it's always best to post
CREATE TABLE statements for the tables, INSERT statements for some
sample data, and then the results you expect from the sample data.
That way other people can quickly post your script into Query Analyzer
and run it, and code is usually clearer than a description.

Simon

[--CELKO--]

The order of execution of infixed operators is left to right,
following the parentheses in the usual manner. The ON clause
associates with the nearest JOIN operator.

If you give a table expression a name, then only that name is seen by
containing expressions. For example, only Foobar is seen by the
SELECT and the WHERE, not Foo or Bar.

SELECT ..
FROM (Foo LEFT OUTER JOIN Bar ON x = y) AS Foobar(w,x,y,z)
WHERE ..;

Since you did not bother to post DDL, all anyone can do is guess, but
it looks like each employee has an optional assigned stand-in. The
query is to return a list of them.

Why is the Departments table here at all?? Just use the S1 department
code. And if you only have one department, as shown by the singular
name, then leave it out of the schema altogether (I assume that is
another naming error in the data model). I also got rid of those
silly "tbl-" that some passing axe murderer has added in violation of
ISO-11179 standards.

SELECT S1.staffno, S1.standin, S1.firstname, S1.lastname,
S1.dept, S1.telephone, S1.worktelephone,
S2.firstname AS firstname2, S2.lastname AS lastname2
FROM (Staff AS S1
LEFT OUTER JOIN
Staff AS S2
ON S1.standin = S2.staffno)
WHERE S1.ntuser = @ntuser;

[Ross Presser]

On 12 Jul 2004 01:50:15 -0700, kieran wrote:

I have the following sql statement. I originally had the statement
with two INNER JOINS but in some situations was getting an error so
changed the last INNER JOIN to a LEFT OUTER JOIN (as is seem below).
This seemed to work but i am unsure why and would like to know in case
it falls over again. Why did the two INNER JOINS not work, and am I
correct to use the LEFT OUTER JOIN in this context.
As you can see the table 'tblStaff1_2' does not exist so I used the
line 'tblStaff tblStaff1_2' to create it temporarily. Does this have
something to do with it.

As Simon Hayes said, you really need to tell us more about how it didn't
work, and supply CREATE TABLE and INSERT statements to give us an idea of
the situation.

That said, and totally guessing from the nature of your table and field
names ... you seem to be trying to assemble information from the staff
members, the department name they belong to, and who the standin person is,
about the person currently logged in at the machine. My guess is that the
error you got was when you got no matching row if the person did not have a
standin defined.

If this was your problem, then a LEFT OUTER JOIN is overkill, and a LEFT
INNER JOIN will suffice. What this does is tell SQL that even if there is
no row that matches in the second table, you still want the result from the
first table.

the clause "tblStaff tblStaff1_2" means that you want SQL to look at the
tblStaff table *twice*, once as the main table, and once as the left-joined
table .. so you need to give it an alternate name for the second usage.

[Erland Sommarskog]

Ross Presser (rp******@imtek.com) writes:

If this was your problem, then a LEFT OUTER JOIN is overkill, and a LEFT
INNER JOIN will suffice.
LEFT INNER JOIN?

Maybe that works on some other DBMS, but when I fed SQL Server with:

SELECT * FROM a LEFT INNER JOIN b ON a.col = b.col

It replied:

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'INNER'.
What this does is tell SQL that even if there is no row that matches in
the second table, you still want the result from the first table.

That sounds very much like an outer join to me...

Maybe you confused LEFT OUTER JOIN with FULL [OUTER] JOIN?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[kieran]

Sorry about not posting the create or insert, my fault entirely. I am
not a regular sql developer and did not know the standard.

On detailed inspection, it appears that the code was falling over on
the StandIn problem. Thanks for that.

In future if i post code, i will post the create and insert as well.
Did not mean to be lazy.

Thanks for all help and insight.




Ross Presser <rp******@imtek.com> wrote in message news:<11*****************************@40tude.net>. ..

On 12 Jul 2004 01:50:15 -0700, kieran wrote:

I have the following sql statement. I originally had the statement
with two INNER JOINS but in some situations was getting an error so
changed the last INNER JOIN to a LEFT OUTER JOIN (as is seem below).
This seemed to work but i am unsure why and would like to know in case
it falls over again. Why did the two INNER JOINS not work, and am I
correct to use the LEFT OUTER JOIN in this context.
As you can see the table 'tblStaff1_2' does not exist so I used the
line 'tblStaff tblStaff1_2' to create it temporarily. Does this have
something to do with it.

As Simon Hayes said, you really need to tell us more about how it didn't
work, and supply CREATE TABLE and INSERT statements to give us an idea of
the situation.

That said, and totally guessing from the nature of your table and field
names ... you seem to be trying to assemble information from the staff
members, the department name they belong to, and who the standin person is,
about the person currently logged in at the machine. My guess is that the
error you got was when you got no matching row if the person did not have a
standin defined.

If this was your problem, then a LEFT OUTER JOIN is overkill, and a LEFT
INNER JOIN will suffice. What this does is tell SQL that even if there is
no row that matches in the second table, you still want the result from the
first table.

the clause "tblStaff tblStaff1_2" means that you want SQL to look at the
tblStaff table *twice*, once as the main table, and once as the left-joined
table .. so you need to give it an alternate name for the second usage.

[Ross Presser]

On Mon, 12 Jul 2004 21:48:40 +0000 (UTC), Erland Sommarskog wrote:

Ross Presser (rp******@imtek.com) writes:

If this was your problem, then a LEFT OUTER JOIN is overkill, and a LEFT
INNER JOIN will suffice.

LEFT INNER JOIN?

Maybe that works on some other DBMS, but when I fed SQL Server with:

SELECT * FROM a LEFT INNER JOIN b ON a.col = b.col

It replied:

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'INNER'.

What this does is tell SQL that even if there is no row that matches in
the second table, you still want the result from the first table.

That sounds very much like an outer join to me...

Maybe you confused LEFT OUTER JOIN with FULL [OUTER] JOIN?

I have no idea what I was thinking. Please ignore me for the next month.

[Joe Celko]

>> If this was your problem, then a LEFT OUTER JOIN is overkill, and a
LEFT INNER JOIN will suffice. <<

1) Ain't no such animal as a LEFT INNER JOIN; looks like a cut & paste
problem :)

2) If some of the staff have no stand-ins as shown by a NULL, then
better to get some NULLs than to skip them completely. But without DDL,
we can only guess.

the clause "Staff AS Staff1_2" means that you want SQL to look at the

tblStaff table *twice*, once as the main table, and once as the
left-joined table .. so you need to give it an alternate name for the
second usage. <<

That is not the model we use in the Standard. The system is to act as
if a new table is materialized with the name on the right side of the AS
operator and all it's rows are copied from the table expression on the
left side of the AS operator. All at once, as a set. And then this table
disappears at the end of the statement.

To say "usage" implies that you still have a file model of data. This is
not anything like the way a 3GL opens the same file with two OPEN
statements and then does READs against it on those channels.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Ross Presser]

On 13 Jul 2004 20:56:19 GMT, Joe Celko wrote:

1) Ain't no such animal as a LEFT INNER JOIN; looks like a cut & paste
problem :)

The cut and paste problem was in my brain. Sorry.