Sub-queries assistance, please/

[HeloWorld]

Hi Everyone,

For simplyfying things, let's assume that I have two tables MAIN and USERS

USERS contains the fileds ID, FIRSTNAME, LASTNAME
MAIN contains AUTHOR, EDITEDBY, APPROVEDBY

My question is, how do I retrieve the three instances of FIRSTNAME and
LASTNAME?
I assume I need to use sub-queries, but have never successfully used them
before...

[Dan Bracuk]

Your question is very vague. Here is a specific question. Firstname and Lastname of what?

[HeloWorld]

Hi Dan,

What I am trying to do is return the first name and last name of three
separate people.

How do I return multilple values of FIRSTNAME / LASTNAME?
One that corresponds to MAIN.AUTHOR and MAIN.EDITEDBY and MAIN.APPROVEDBY?

Eg. MAIN.AUTHOR = '1', MAIN.EDITEDBY = '2' etc.

USERS table contains ID, FIRSTNAME, LASTNAME
1, fred, nurk
2, john, smith

I need a result set that allows john smith and fred nurk to appear on the same
row.

I was originally thinking of using a subquery... but perhaps I need to use
Tables aliases? i really don't know - thus my asking :)

[MikerRoo]

Something like this will work on most databases. Different DB's allow more
efficient methods:
(I won't cover the left join case in this thread.)

<b>
SELECT
tAuth.FirstName AS AuthNameFirst
, tAuth.LastName AS AuthNameLast
, tEdit.FirstName AS EditNameFirst
, tEdit.LastName AS EditNameLast
, tApprv.FirstName AS ApprvNameFirst
, tApprv.LastName AS ApprvNameLast
, tMain.AUTHOR <!--- Add any main column like this. --->
FROM
MAIN tMain
, USERS tAuth
, USERS tEdit
, USERS tApprv
WHERE
tMain.AUTHOR = tAuth.ID
AND
tMain.EDITEDBY = tEdit.ID
AND
tMain.APPROVEDBY = tApprv.ID
</b>

[Dan Bracuk]

Given this:
USERS table contains ID, FIRSTNAME, LASTNAME
1, fred, nurk
2, john, smith

How do I know who is an author, editor, etc?