Here's a contrived example:

TABLE OBJECTS
ObjectID ObjectType    ObjectName    CreatedBy    ApprovedBy
1                Staff            Bob                 1                 1
2                Staff            Jane                1                 1
3                Order         Order1               2                 1

SELECT
    a.ObjectName as OrderName,
    (SELECT b.ObjectName FROM Objects b WHERE a.CreatedBy = b.ObjectID) AS
CreatorName,
    (SELECT c.ObjectName FROM Objects c WHERE a.ApprovedBy = c.ObjectID) AS
ApproverName
FROM
    Objects a
WHERE
    a.ObjectID = 3

should return

OrderName        CreatorName            ApproverName
Order1                Jane                            Bob

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Ken Schaefer" <ken@a...>
Subject: [sql_language] RE: Using 'as' in a query


: I took that to mean that they are separate rows in the same, but related,
: table...
:
: but, even if they are in the same table as the rest of the data, you can
: still use a subquery - you just need to give the table a different alias
for
: each subquery.
:
: Cheers
: Ken
:
: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: "David Cameron" <dcameron@i...>
: Subject: [sql_language] RE: Using 'as' in a query
:
:
: Except for the fact that he said:
:
: > Both names are in the same field but different
: > rows of the same table.
:
: regards
: David Cameron
: nOw.b2b
: dcameron@i...
:
: >  -----Original Message-----
: > From: Ken Schaefer [mailto:ken@a...]
: > Sent: Tuesday, 10 December 2002 5:06 PM
: > To: sql language
: > Subject: [sql_language] RE: Using 'as' in a query
: >
: > If you have something like an Orders table, and there is a CreatorID,
and
: an
: > ApproverID, and both these IDs refer to StaffIDs in the Staff table,
then
: > you can do it:
: >
: > SELECT
: >     a.OrderID,
: >     a.OrderDate
: >     (SELECT StaffName FROM Staff b WHERE b.StaffID = a.CreatorID) AS
: > CreatorName,
: >     (SELECT StaffName FROM Staff c WHERE c.StaffID = a.ApproverID) AS
: > ApproverName
: > FROM
: >     Orders
: > WHERE
: >     a.OrderID = 1
: >
: > Cheers
: > Ken
: >
: > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: > From: "David Cameron" <dcameron@i...>
: > Subject: [sql_language] RE: Using 'as' in a query
: >
: >
: > No can do. You are trying to pretend that two rows are one row and it
: > doesn't work. You have to iterate through the resultset.
: >
: > regards
: > David Cameron
: > nOw.b2b
: > dcameron@i...
: >
: > >  -----Original Message-----
: > > From: Peter Scribner [mailto:plscribner@w...]
: > > Sent: Tuesday, 10 December 2002 11:27 AM
: > > To: sql language
: > > Subject: [sql_language] Using 'as' in a query
: > >
: > > Hi all,
: > >
: > > I have a query wherein I need to get the record creator's name AND the
: > > record approver's name.  Both names are in the same field but
different
: > > rows of the same table.  One way I thought of retrieving them was to
: alias
: > > the fields being returned so that one is called 'c' and the other 'A'.
: > > Doesn't want to let me do that.
: > >
: > > Any assistance you can provide would be most appreciated.
: > >
: > > Here's the query:
: > >
: > > cmdTemp.CommandText="SELECT Distinct (po.ponumber), po.PODate, " _
: > > & "po.POCreator, po.POStatus , " _
: > > & " Vendors.VName, Users.UserLName as c, Users.UserLName as A From " _
: > > & "PurchaseOrders po,Vendors, Users Where " _
: > > & "ContractorID="&CID&"  AND Vendors.VendorID= po.VendorID AND " _
: > > & "Users.UserID=po.POCreator And A=po.Approver order by po.PONumber"
: > >
: > > The query works fine with Users.UserID unaliased but then I can't the
: two
: > > different names.
: > >
: > > Thanks again for your help.
: >
: >
: >
:
:
: