473,379 Members | 1,323 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,379 software developers and data experts.

SQL Join Statement problem

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')
Jul 20 '05 #1
13 1881
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)
Jul 20 '05 #2


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!
Jul 20 '05 #3
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
Jul 20 '05 #4
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)
Jul 20 '05 #5
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)
Jul 20 '05 #6
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
Jul 20 '05 #7
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)
Jul 20 '05 #8
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)
Jul 20 '05 #9
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.
Jul 20 '05 #10
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.
Jul 20 '05 #11
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.
Jul 20 '05 #12
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)
Jul 20 '05 #13
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.
Jul 20 '05 #14

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

Similar topics

6
by: Allan | last post by:
Please help, below is my problem. Let's say I have 2 tables, a Products table and a Colors table that go as follow: Table Products prodID Name 1 shirt 2 tshirt
4
by: algroth | last post by:
Hi! I try to do a nested inner join by sql, but I always get an "Syntax-error in FROM-clause". The statement I try around with is: SELECT * FROM st1 INNER JOIN st2 INNER JOIN st3
14
by: Ryan | last post by:
I want to do the following SQL statement in Access. However, it won't allow me to have the secondary part of my join statement and tells me that this is not supported. OK, so Access doesn't support...
4
by: Jean-Claude | last post by:
Hi, which is the faster query ? (of course, in my case the real queries are more complex) 1/ select * from file1 a join file2 b on b.key=a.key where b.data=123 and b.name='TEST'
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...
5
by: Sascha.Moellering | last post by:
Hi, I receive the error code SQL0338N if I try to compile this statement (part of the statement): .... left outer join lateral (SELECT * FROM LIZSYSABA.VWZL0359TBS WHERE tbs_name = CASE WHEN...
3
by: rrstudio2 | last post by:
If I have two tables and need to do a left outer join and include a where statement on the second table, it seems like the left outer join becomes an inner join. For example: Table: Names id...
4
by: linendra | last post by:
Hi i am using oracle 8i. when i execute the join statement i got the error message. at the same time when i execute the same code to sql server and ms access the code execute successfully. i...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
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: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
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?
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...

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.