Join Question

[PeteBWhat]

Hi there,

I'm trying to figure out if it's possible to import the firstName and lastName from a users table for 4 separate userids using a join. I was able to do it for one of the fields, but I can't figure out how to do it for the other three... here's an example of the SQL code and the output...

#
SELECT leads.leadid,leads.wordCount,leads.workingTitle,leads.createdBy,leads.modifyBy,leads.assignedOwner,issues.issueName,sections.sectionname,
#
CONCAT(users.firstName, ' ', users.lastName) AS sectionEditor FROM leads
#
LEFT JOIN sections ON leads.sectionid = sections.sectionid
#
LEFT JOIN issues ON leads.issueid = issues.issueid
#
LEFT JOIN users ON sections.sectionEditor = users.userid;
#

#
+--------+-----------+--------------+-----------+----------+---------------+----------------------+---------------+----------------+
#
| leadid | wordCount | workingTitle | createdBy | modifyBy | assignedOwner | issueName            | sectionname   | sectionEditor  |
#
+--------+-----------+--------------+-----------+----------+---------------+----------------------+---------------+----------------+
#
|      2 |       300 | Title        |         1 |        0 |            21 | Fall 2005, Issue 5   | Cartoon       | Chuck Harrison |
#
|      3 |       300 | Title        |         1 |        0 |            21 | Fall 2005, Issue 5   | Cartoon       | Chuck Harrison |
#
|      4 |       300 | Title        |         1 |        0 |            21 | Fall 2005, Issue 5   | Cartoon       | Chuck Harrison |
#
|      5 |       300 | Title        |         1 |        0 |            21 | Fall 2005, Issue 5   | Cartoon       | Chuck Harrison |
#
|      6 |       300 | Title        |         1 |        0 |            21 | Fall 2005, Issue 5   | Cartoon       | Chuck Harrison |
#
|      7 |       300 | Title        |         1 |        0 |            21 | Fall 2005, Issue 5   | Cartoon       | Chuck Harrison |
#
|      8 |       300 | Title        |         1 |        0 |            21 | Fall 2005, Issue 5   | Cartoon       | Chuck Harrison |
#
|      9 |       300 | Title        |         1 |        0 |            21 | Fall 2005, Issue 5   | Cartoon       | Chuck Harrison |
#
|     10 |       300 | Title        |         1 |        0 |            21 | Fall 2005, Issue 5   | Cartoon       | Chuck Harrison |
#
|     11 |       300 | Lead Title   |         1 |        0 |            23 | Spring 2006, Issue 2 | News & Events | Tim Flynn      |
#
|     12 |       300 | Lead Title   |         1 |        0 |            23 | Spring 2006, Issue 2 | News & Events | Tim Flynn      |
#
+--------+-----------+--------------+-----------+----------+---------------+----------------------+---------------+----------------+

(Better formatted version at http://pastebin.com/411584)

I'm trying to replace createdBy modifyBy and assignedOwner with the firstName lastName fields from the users table like I did for sectionEditor -- I'm just not sure how.

Any suggestions?

[coreyp_1]

if you are using mysql 4.1+, you can use subqueries to accomplish this task

[nemonoman]

you would alias the table users 4 times:

CONCAT(a.firstName, ' ', a.lastName),
CONCAT(b.firstName, ' ', b.lastName),
CONCAT(c.firstName, ' ', c.lastName),
CONCAT(d.firstName, ' ', d.lastName),FROM leads, users a, users b, users c, users d

WHERE sections.sectionEditor =a.userid
AND someotherId=b.userid
AND anotherID=c.userid
AND stillyetanotherID=d.userid

etc.