Its not too hard, just use aliases inside your table joins. You do a
left join so that you will still get a result even if the
deletedbyuserIDFK is null / blank
You could also add some case logic in there so you don't have blanks
(example on the delete user column
SELECT
A.ID,
A.Username,
B.Username as insertUser,
C.username as editUser,
CASE WHEN d.id IS NOT NULL THEN D.username ELSE 'None' END as
deleteUser
FROM
Users A LEFT JOIN users B ON A.insertByUserIDFK = B.ID
LEFT JOIN users C ON A.editbyuserIDFK = C.ID
LEFT JOIN users D on A.deletedbyuserIDFK = D.ID
Chris Peterson
-----Original Message-----
From: Michael van Leest [mailto:[EMAIL PROTECTED]
Sent: Monday, November 24, 2008 8:54 AM
To: sql
Subject: multiple joins on 1 table help!
Hi all,
It seems like my last message didn't came through, so here it is again:
It's been a while since I've done any SQL work, so I need some
help/pointers for the following problem:
I have 1 table with users with a ID, username, insertbyuserIDFK,
editbyuserIDFK and deletedbyuserIDFK (and some more irrelevant columns).
The query needs to get the users with the usernames of the user that
inserted, edited and if filled in, deleted the user entry.
Any help on this is appreciated!
Thanks, Michael
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3156
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6
