Returning multiple values with SQL query

  • Thread starter Thread starter AccessNewbie
  • Start date Start date
A

AccessNewbie

I have a database with a couple of table. The main table I am working with
(named PR) has one field for the project manager's id number ("projmgr") and
one field for the project supervisor's id number ("supervisor").

The name of the project manager and supervisor are in a second table (named
EM). This table has fields for first name ("firstname"), last name
("lastname"), middle name ("middlename") and id number ("employee").

I would like to do one SELECT statement that will return, among other things
the names of both the project manager and supervisor.

So far I have the following SELECT statement:

SELECT pr.wbs1, pr.wbs2, pr.wbs3, pr.longname, pr.projmgr,
pr.supervisor, pr.clientid, pr.fee, pr.reimballow, pr.consultfee,
pr.org, projectcustomtabfields.custhpprojectno as HPNum,
em.lastname, em.firstname, em.middlename, cl.name as ClientName,
organization.name as OfficeName FROM pr
LEFT JOIN projectcustomtabfields on (pr.wbs1=projectcustomtabfields.wbs1
and pr.wbs2=projectcustomtabfields.wbs2 and
pr.wbs3=projectcustomtabfields.wbs3)
LEFT JOIN organization on pr.org= organization.org
LEFT JOIN em on pr.projmgr=em.employee
LEFT JOIN cl on pr.clientid=cl.clientid
WHERE pr.status='A' and pr.org='6501'
ORDER BY pr.wbs1, pr.wbs2, pr.wbs3

This only returns the name of the project manager. How can I also get the
name of the supervisor?

thanks
 
You would need to add the em table into the query one more time and join it
to the supervisor field. Then alias your firstname and lastname fields so
you know which is which.
 
thanks. However, I'm not sure how this should be done. could you possibly
edit the select statement as you suggested? thanks
 
Something like the following:

SELECT pr.wbs1, pr.wbs2, pr.wbs3, pr.longname, pr.projmgr,
pr.supervisor, pr.clientid, pr.fee, pr.reimballow, pr.consultfee,
pr.org, projectcustomtabfields.custhpprojectno as HPNum,
em.lastname, em.firstname, em.middlename, cl.name as ClientName,

ESuper.LastName as SprLast, ESuper.FirstName as SprFirst, ESuper.Middlename
as SprMiddle,

organization.name as OfficeName
FROM ((((pr
LEFT JOIN projectcustomtabfields on (pr.wbs1=projectcustomtabfields.wbs1
and pr.wbs2=projectcustomtabfields.wbs2 and
pr.wbs3=projectcustomtabfields.wbs3))
LEFT JOIN organization on pr.org= organization.org)
LEFT JOIN em on pr.projmgr=em.employee)
LEFT JOIN cl on pr.clientid=cl.clientid)

LEFT JOIN Em as ESuper on PR.Supervisor = ESuper.Employee

WHERE pr.status='A' and pr.org='6501'
ORDER BY pr.wbs1, pr.wbs2, pr.wbs3
 
thanks, that does it. much appreciated.

John Spencer said:
Something like the following:

SELECT pr.wbs1, pr.wbs2, pr.wbs3, pr.longname, pr.projmgr,
pr.supervisor, pr.clientid, pr.fee, pr.reimballow, pr.consultfee,
pr.org, projectcustomtabfields.custhpprojectno as HPNum,
em.lastname, em.firstname, em.middlename, cl.name as ClientName,

ESuper.LastName as SprLast, ESuper.FirstName as SprFirst, ESuper.Middlename
as SprMiddle,

organization.name as OfficeName
FROM ((((pr
LEFT JOIN projectcustomtabfields on (pr.wbs1=projectcustomtabfields.wbs1
and pr.wbs2=projectcustomtabfields.wbs2 and
pr.wbs3=projectcustomtabfields.wbs3))
LEFT JOIN organization on pr.org= organization.org)
LEFT JOIN em on pr.projmgr=em.employee)
LEFT JOIN cl on pr.clientid=cl.clientid)

LEFT JOIN Em as ESuper on PR.Supervisor = ESuper.Employee

WHERE pr.status='A' and pr.org='6501'
ORDER BY pr.wbs1, pr.wbs2, pr.wbs3
Click to expand...
 
Back
Top