473,389 Members | 1,346 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,389 software developers and data experts.

Inner Join question

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)

--------------------------
Jul 20 '05 #1
8 6299
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
Jul 20 '05 #2
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;
Jul 20 '05 #3
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.
Jul 20 '05 #4
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
Jul 20 '05 #5
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.

Jul 20 '05 #6
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.
Jul 20 '05 #7
>> 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!
Jul 20 '05 #8
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.
Jul 20 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Ike | last post by:
Oh I have a nasty query which runs incredibly slowly. I am running MySQL 4.0.20-standard. Thus, in trying to expedite the query, I am trying to set indexes in my tables. My query requires four...
3
by: Prem | last post by:
Hi, I am having many problems with inner join. my first problem is : 1) I want to know the precedance while evaluating query with multiple joins. eg. select Employees.FirstName,...
1
by: Steve | last post by:
Hi, I am real new to databases and hoping someone can help. Main-table is a huge spreadsheet that I imported into Access Site-table has a bunch of addresses pulled from Main-table, quite a few...
6
by: dmonroe | last post by:
hi group -- Im having a nested inner join problem with an Access SQl statement/Query design. Im running the query from ASP and not usng the access interface at all. Here's the tables: ...
52
by: MP | last post by:
Hi trying to begin to learn database using vb6, ado/adox, mdb format, sql (not using access...just mdb format via ado) i need to group the values of multiple fields - get their possible...
3
by: Zeff | last post by:
Hi all, I have a relational database, where all info is kept in separate tables and just the id's from those tables are stored in one central table (tblMaster)... I want to perform a query, so...
12
by: Chamnap | last post by:
Hello, everyone I have one question about the standard join and inner join, which one is faster and more reliable? Can you recommend me to use? Please, explain me... Thanks Chamnap
1
by: teneesh | last post by:
Here I have a code for a view that has been created by a developer on my team. I am trying to use the very same code to create a view for a different formid/quesid. But I cannot figure out how this...
2
by: MATTXtwo | last post by:
I have this store procedure to select data from table with join like this...SELECT tblPeribadi.Personel_No, tblPeribadi.Nama,tblCompany.Keterangan as Company_Code, tblPeribadi.Jawatan,...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.