In MySQL 4.1.12 I have these 2 tables (reps and apps).  I need to do a
query that will select all 'apps' matching certain criteria and at the
same time include information from 'reps'.

Here is the structure of reps:
repID
firstName
lastName

And the pertinent info from apps:
agentID
closeID
verID
(60 other columns)

the results should give me the 60 columns, plus instead of those ID's,
give me the agentlastName, agentfirstName, closelastName,
closefirstName, verlastname, verfirstname.  I know how to do a JOIN
and have it include 1 reps information, but unsure how to get it to
include 3 different reps name's in each result.

Here is my work in progress sample SQL query, the portion after the
WHERE I believe is OK (or it was before I added the JOIN).  I know the
part before my JOIN is incorrect, it's only grabbing one of the names:

SELECT reps.lastName, reps.firstName, apps.* from apps LEFT JOIN reps
ON reps.repID=apps.agentID OR reps.repID=apps.closeID OR
reps.repID=apps.verID where (lastUpdateDateTime BETWEEN '2006-01-26
00:00:00' and '2006-01-29 23:59:00') AND (agentID IN (4,1) OR closeID
IN (4,1) OR verID IN (4,1)) AND (agentCRC IN ('OK','NFO') OR closeCRC
IN ('OK','NFO') OR verCRC IN ('OK','NFO'))

It's boggling my mind on how to rewrite this.  Help please!!  :-)

By using alias's you could inlcude all three rep names.  Here is an exmaple:

SELECT reps_agent.firstName, reps_agent.lastName,
reps_close.firstName, reps_close.lastName, reps_ver.firstName,
reps_ver.lastName, apps.*
FROM reps AS reps_ver RIGHT JOIN (reps AS reps_close RIGHT JOIN (reps
AS reps_agent RIGHT JOIN apps ON reps_agent.repID = apps.agentID) ON
reps_close.repID = apps.closeID) ON reps_ver.repID = apps.verID
where ...

I join reps three times each with a different alias.  I hope this helps.

Thanks for that!!!

It's working, in that it now retrieves the 3 sets of names.  Is there
a way to have it rename the field names at the same time.  See..now in
the results I get:

firstName,lastName,firstName,lastName,firstName,lastname,agentID,closeID,verID,etc....

I haven't tried yet, because I wanted to try and get you while you are
still around but I'm wondering if something like this would work(I'm
no SQL pro):

SELECT reps_agent.firstName as agentfirstName, reps_agent.lastName as
agentlastName, .....

That works!!  I just tried it. :-)

OK thanks a HUGE bunch wilde_lawson!!!!!

I owe you!!!