multiple joins on 1 table help!

[Michael van Leest]

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:3155
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Peterson, Chris]

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:mvanleest@...]
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=17837.14401.6