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)