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.

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.

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)

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.