SQL Join Statement problem

[kieran]

Hi,

I have the following SQL statement which is pulling a few details from
a database. As you can see, there is only the one table from which i
am creating a temporary copy.
The reason I do this is because in the table i only have the 'standIn'
listed by integer and i want to return the 'standIn' by name.

I hope this is clear enough.

The statement works but i am now noticing that it lists multiple
returns in SQL Analyser e.g it is listing three different rows for one
user and these have all been past StandIns for the user in question.
It is not a problem at the moment but it may be and i would like to
know why it is doing this. Can i change the statement to stop this, i
have been messing with the join part but no luck.

Any help greatly appreciated.

-----------------------------------------------------
SELECT T2.FirstName AS StandIn_FirstName, T2.LastName AS
StandIn_LastName
FROM tblStaff AS T1
LEFT OUTER JOIN tblStaff AS T2
ON T1.StaffNo = T2.StandIn
WHERE (T1.NTUserName = 'auser')

[Hugo Kornelis]

On 11 Aug 2004 04:57:11 -0700, kieran wrote:

Hi,

I have the following SQL statement which is pulling a few details from
a database. As you can see, there is only the one table from which i
am creating a temporary copy.
The reason I do this is because in the table i only have the 'standIn'
listed by integer and i want to return the 'standIn' by name.

I hope this is clear enough.

The statement works but i am now noticing that it lists multiple
returns in SQL Analyser e.g it is listing three different rows for one
user and these have all been past StandIns for the user in question.
It is not a problem at the moment but it may be and i would like to
know why it is doing this. Can i change the statement to stop this, i
have been messing with the join part but no luck.

Any help greatly appreciated.

-----------------------------------------------------
SELECT T2.FirstName AS StandIn_FirstName, T2.LastName AS
StandIn_LastName
FROM tblStaff AS T1
LEFT OUTER JOIN tblStaff AS T2
ON T1.StaffNo = T2.StandIn
WHERE (T1.NTUserName = 'auser')

Hi Kieran,

Hard to tell without knowing anything about the table structure and data
in your database. Please post the following:

1. Table structure, in the form of DDL (CREATE TABLE statements, including
all constraints; irrelevant columns may be omitted);
2. Sample data (in the form of INSERT stattements);
3. The output you'd like to see, based on the sample data provided;
4. A description of the business problem you're trying to solve.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[kieran h]

Hi Hugo,

Here is the create table, update and select statements - im not sure
what u mean by ddl, but all the staements you will need to test it are
here.
This is really frustrating me now because it returns a single row with
the select statement. However when you do an update after this
(changing the standIn integer) it returns two rows sometimes. It does
not seem to follow a certain pattern. It doesnt do it if you put all
the updates in together and then do a select. Doing an update and then
trying the select statement sometimes brings back one row and sometimes
two.

Maybe I am missing something really obvious

Thanks for all help.

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

CREATE TABLE [dbo].[tblStaff] (
[StaffNo] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StandIn] [int] NULL ,

) ON [PRIMARY]
GO
-------------------------------------
Insert into tblstaff
values ('fname1', 'lname2', 2)

Insert into tblstaff
values ('fname1', 'lname2', 1)

Insert into tblstaff
values ('fname1', 'lname2', 1)
----Do individually after here----
UPDATE tblstaff
SET StandIn = 3
WHERE StaffNo = 2

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

SELECT T2.FirstName AS StandIn_FirstName, T2.LastName AS
StandIn_LastName
FROM tblStaff AS T1
LEFT OUTER JOIN tblStaff AS T2
ON T1.StaffNo = T2.StandIn
WHERE (T1.StaffNo = 2)
-------------------------------------

UPDATE tblstaff
SET StandIn = 3
WHERE StaffNo = 2

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

[Erland Sommarskog]

kieran h (ki********@hotmail.com) writes:

Here is the create table, update and select statements - im not sure
what u mean by ddl, but all the staements you will need to test it are
here.
DDL = Data Definition Language. Hugo hasn't learnt to speak to less
experienced users. Or he just being snobbish. Anyway, CREATE TABLE
is what he was after, so you got it right to far.
This is really frustrating me now because it returns a single row with
the select statement. However when you do an update after this
(changing the standIn integer) it returns two rows sometimes. It does
not seem to follow a certain pattern. It doesnt do it if you put all
the updates in together and then do a select. Doing an update and then
trying the select statement sometimes brings back one row and sometimes
two.

Yeah, but the rule is that you provide the script that demonstrates
the problem, and leave the analysis to the group. Also, for a case
like this it's a good idea to supply the desired output.

Also, for the sake of the example, it's probably better to not have
StaffNo as an IDENTITY column, so you know which value is which.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

[Hugo Kornelis]

On Wed, 11 Aug 2004 22:01:46 +0000 (UTC), Erland Sommarskog wrote:

kieran h (ki********@hotmail.com) writes:

Here is the create table, update and select statements - im not sure
what u mean by ddl, but all the staements you will need to test it are
here.

DDL = Data Definition Language. Hugo hasn't learnt to speak to less
experienced users. Or he just being snobbish.

Am not. This is what I posted:

.....
1. Table structure, in the form of DDL (CREATE TABLE statements, including
all constraints; irrelevant columns may be omitted);
.....

By the way, I'm glad you replied to kiera, as his message didn't show up
on my news service. I have now found it on google, so I can look at it.

I'll let the supernews guys know about this disappearing post.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Hugo Kornelis]

On 11 Aug 2004 04:57:11 -0700, kieran wrote:

(snip)

Hi Kieran,

As I just wrote in a reply to Erland's message, I didn't catch your
message before as it was somehow blocked or dropped by me news service.
But after reading Erlands message, I managed to find your message on
google.

I copied and pasted the script you posted and it worked just fine. I get
one row of output consistently. Somehow, I don't manage to reproduce the
behaviour you describe (returning sometimes 1, sometimes 2 rows).

Maybe it would help if you could post the expected output as well, in
addition to the CREATE TABLE and INSERT statements you already provided. I
am not sure what you are trying to accomplish; seeing the output you try
to get might help me get a better understanding of your problem.

(Fingers crossed, hoping your next reply will get through...)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[K Finegan]

Hi Kieran,

When I ran the sql you provided and then ran

UPDATE tblstaff
SET StandIn = 2
WHERE StaffNo = 3

& then:

SELECT * FROM tblstaff
SELECT T2.StaffNo, T2.FirstName AS StandIn_FirstName, T2.LastName AS
StandIn_LastName
FROM tblStaff AS T1 LEFT OUTER JOIN tblStaff AS T2
ON T1.StaffNo = T2.StandIn
WHERE (T1.StaffNo = 2)
I get:

StaffNo|FirstName|LastName|StandIn
1 fname1 lname2 2
2 fname1 lname2 3
3 fname1 lname2 2
StaffNo|StandIn_FirstName|StandIn_LastName
1 fname1 lname2
3 fname1 lname2
Anytime the WHERE clause has T1.StaffNo = X & X is a stand-in twice
then two rows appear.

Your query is returning all the times that the StaffNo is a standin &
who they are a standin to not who is a stand in for a particular
StaffNo.

I'm guessing that you want the latter rather than the former.

To do that either reverse the ON T1.StaffNo = T2.StandIn to ON
T1.StandIn = T2.StaffNo or change the where to (T2.StaffNo = 2)

Hope this helps,

K Finegan

[kieran]

Hi Guys,

I think I have an example of where it happens now. If you paste all
the first statement into Query Analyser and run it. Then paste the
second select statement into query analyser you will see two rows
returned.

I think this is the easiest way to see what I am talking about. RE:
where it is used - the tblStaff is a large table with many fields (i
detailed the basic for claity) where all staff details are pulled
from. There is many users on it and I was worried when I saw this why
it was happening. And at this stage im also very curious why this is
happening. Hope you can see what I mean as i know im not going crazy.

Cheers.

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

CREATE TABLE [dbo].[tblStaff] (
[StaffNo] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StandIn] [int] NULL ,

) ON [PRIMARY]
GO

Insert into tblstaff
values ('fname1', 'lname2', 2)

Insert into tblstaff
values ('fname1', 'lname2', 1)

Insert into tblstaff
values ('fname1', 'lname2', 1)
UPDATE tblstaff
SET StandIn = 1
WHERE StaffNo = 2
UPDATE tblstaff
SET StandIn = 1
WHERE StaffNo = 2
UPDATE tblstaff
SET StandIn = 2
WHERE StaffNo = 2
UPDATE tblstaff
SET StandIn = 2
WHERE StaffNo = 2

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

SELECT T2.FirstName AS StandIn_FirstName, T2.LastName AS
StandIn_LastName
FROM tblStaff AS T1
LEFT OUTER JOIN tblStaff AS T2
ON T1.StaffNo = T2.StandIn
WHERE (T1.StaffNo = 2)

[Hugo Kornelis]

On 12 Aug 2004 03:47:15 -0700, kieran wrote:

Hi Guys,

I think I have an example of where it happens now. If you paste all
the first statement into Query Analyser and run it. Then paste the
second select statement into query analyser you will see two rows
returned.

I think this is the easiest way to see what I am talking about. RE:
where it is used - the tblStaff is a large table with many fields (i
detailed the basic for claity) where all staff details are pulled
from. There is many users on it and I was worried when I saw this why
it was happening. And at this stage im also very curious why this is
happening. Hope you can see what I mean as i know im not going crazy.

Cheers.

(snip DDL, sample data and query - thanks for providing it!)

Yes, this will indeed return two rows. They are two DIFFERENT rows,
though, not two copies of the same row. If you want to see more clearly
what's happening, change your query to read:

SELECT T2.FirstName AS StandIn_FirstName, T2.LastName AS
StandIn_LastName, T2.StaffNo
FROM tblStaff AS T1
LEFT OUTER JOIN tblStaff AS T2
ON T1.StaffNo = T2.StandIn
WHERE (T1.StaffNo = 2)

(That is: add T2.StaffNo to the select-list).

You'll see that the two rows returned are for staffno 1 and 2.

First, let's find out what exactly the contents of the table is after the
updates but before the select (leaving out the names - they are the same
on each row and won't influence the results)

SELECT StaffNo, StandIn
FROM tblStaff

StaffNo StandIn
----------- -----------
1 2
2 2
3 1
Here's what the query does (logically speaking - the exact order of
evaluation chosen by SQL Server may differ as long as the results remain
the same).

First, two copies of tblStaff are made in a working area; they are joined
so that a row from T1 will be combined with a row from T2 if the person in
T1 can be a standin for T2. If a row from T1 has no matching row in T2, it
is combined with a bunch of NULL values (as a result of the LEFT OUTER
JOIN). The intermediate results will be (agian leaving out the names):

<----- T1 -----> <----- T2 ----->
StaffNo StandIn StaffNo StandIn
----------- ----------- ----------- -----------
1 2 3 1
2 2 1 2
2 2 2 2
3 1 NULL NULL

The first row denotes that StaffNo 1 is standin for StaffNo 3. The second
and third row denote that StaffNo 2 is standin for StaffNo 2 (him/herself)
and 1. The final row denotes that StaffNo 3 is standin for nobody.

The WHERE clause filters the intermediate results above. Only the rows
with T1.StaffNo = 2 are retained. These are the two rows denoting that
StaffNo 2 is standin for StaffNo 1 and 2.

Finally, the SELECT clause defines what should be returned. In the case of
your original query, this will return the first and last name of the two
employees that have employee 2 as a standin.

While the above (hopefully) clarifies why you get two rows, it doesn't
solve your problem. In order to do that, I really must now what output you
would expect and why you expect that output. Once you post that, I (and
the other regular posters in this group) can try to find you a better
query.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[kieran]

Thanks for your reply Hugo.

Based on ur detailed explanation, i think i see it clearer and also
see where i was going wrong. Basically each person can only have one
standin but u can be a standin to many people. I wanted to get who
the standin was for that particular user. I was getting the two or
more rows returned bcause of the last line of the statement
WHERE (T1.StaffNo = 2)
it should have been
WHERE (T2.StaffNo = 2) and this brings me back the standin for
that person - a single value.

I hope this is right, i will start testing it in the system but i
think thats it.

Cheers for all your help.

[Sippi]

I built the examples as well - the problem as I read it is that we
expected one answer, and got two. My final query/variation follows:

SELECT t1.StaffNo, t2.StaffNo as StandinNo , T2.FirstName AS
StandIn_FirstName, T2.LastName AS
StandIn_LastName
FROM tblStaff AS T1
LEFT OUTER JOIN tblStaff AS T2
ON T1.StaffNo = T2.StandIn
WHERE (T1.StaffNo = 2 and (T1.StaffNo <> T2.StaffNo))

Cheers.

[Sippi]

I built the examples as well - the problem as I read it is that we
expected one answer, and got two. My final query/variation follows:

SELECT t1.StaffNo, t2.StaffNo as StandinNo , T2.FirstName AS
StandIn_FirstName, T2.LastName AS
StandIn_LastName
FROM tblStaff AS T1
LEFT OUTER JOIN tblStaff AS T2
ON T1.StaffNo = T2.StandIn
WHERE (T1.StaffNo = 2 and (T1.StaffNo <> T2.StaffNo))

Cheers.

[Hugo Kornelis]

On 12 Aug 2004 09:57:15 -0700, kieran wrote:

Thanks for your reply Hugo.

Based on ur detailed explanation, i think i see it clearer and also
see where i was going wrong. Basically each person can only have one
standin but u can be a standin to many people. I wanted to get who
the standin was for that particular user. I was getting the two or
more rows returned bcause of the last line of the statement
WHERE (T1.StaffNo = 2)
it should have been
WHERE (T2.StaffNo = 2) and this brings me back the standin for
that person - a single value.

I hope this is right, i will start testing it in the system but i
think thats it.

Cheers for all your help.

Hi Kieran,

Yes, then you would need to use T2.StaffNo = 2. But you will also need to
change the SELECTed columns, 'cause you are now showing the name of
employee #2 and doing nothing with the joined in row from T1 (with the
standin).

To prevent this sort of thing, it's better to use self-describing aliases
if you use the same table more than once in a query. That makes your query
a lot easier to read and understand!

To find the standin for user 2, you could use either one of these queries:

SELECT StandIn.FirstName, StandIn.LastName
FROM tblStaff AS User
INNER JOIN tblStaff AS StandIn
ON StandIn.StaffNo = User.StandIn
WHERE User.StaffNo = 2
(untested)

This will return no rows if user #2 has no standin. If you prefer to get
one row with NULL values for the standin, use the following instead:

SELECT StandIn.FirstName, StandIn.LastName
FROM tblStaff AS User
LEFT JOIN tblStaff AS StandIn
ON StandIn.StaffNo = User.StandIn
WHERE User.StaffNo = 2
(untested)
Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[kieran]

Hi,

At the risk of boring everyone but as it annoys me when people don't
detail everything in a thread for future users. I think I see the
problem.

I previously wrote the below which was wrong as I am looking for the
user details and that user is in the first table.
------------------------
I was getting the two or
more rows returned bcause of the last line of the statement
WHERE (T1.StaffNo = 2)
it should have been
WHERE (T2.StaffNo = 2) and this brings me back the standin for
that person - a single value.
--------------------------

All I am using the second table for is to get the name and address of
the standin based on the standin integer I have in the first table.

Thanks hugo for last post about using clearer names as it does make it
easier. although I will use older format so people can see what i am
saying based on the previous posts. I now think that the mistake in
the statement was at the "On" part.

It should have read -
ON T1.StandIn = T2.Staffno

instead of -
ON T1.StaffNo = T2.StandIn

This means that it now is pulling the standin for that user, not every
case where that user is a standin.

The staement is now -
---------------------------

SELECT T2.FirstName AS StandIn_FirstName, T2.LastName AS
StandIn_LastName, T2.StaffNo
FROM tblStaff AS T1
LEFT OUTER JOIN tblStaff AS T2
ON T1.StandIn = T2.Staffno
WHERE (T1.StaffNo = 2)

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

I am fairly certain that this is it this time. Anyone thinking
differently please correct me. I know the reason this took so long is
my unclear explanation of what i expected from the results. Sorry.

Thanks.