[thelist] SQL Question

[David Mitchell]

Hi Gang,

Thanks to everyone who replied. The solution was actually pretty simple 
once you guys gave some suggestions. I don't have the actual procedure 
here at home now, but I can post it once I get back to work if anyone is 
curious.

Cheers,

Dave

Ken Schaefer wrote:

>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>From: "David Mitchell" <dave at dbmdata.com>
>Subject: [thelist] SQL Question
>
>
>: Not sure the best way to go about this.
>: Here is the scenario:
>:
>: I have a "user" table with a field "NumUser"
>: as the key. I have another table with the fields
>: "CreatedBy" and "ModifiedBy". Each one of these
>: fields will have a "NumUser" value in it (except that
>: ModifiedBy may be null). I am attempting to do a
>: join across these 2 tables to get the FirstName
>: and LastName values for the CreatedBy and
>: ModifiedBy values. Make sense?
>:
>: Any advice is appreciated.
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
>It always helps if you post some sample data, and your expected output.
>
>However, based on what I can gather from your description, you might want
>something like:
>
>SELECT
>    a.UserName
>    (
>        SELECT
>            b.UserName
>        FROM
>            Users b
>        INNER JOIN
>            OtherTable c
>        ON
>            c.CreatedBy = b.NumUser
>        WHERE
>            b.NumUser = a.NumUser
>    ) AS CreatedBy
>    (
>        SELECT
>            d.UserName
>        FROM
>            Users d
>        INNER JOIN
>            OtherTable e
>        ON
>            e.ModifiedBy = d.NumUser
>        WHERE
>            d.NumUser = a.NumUser
>    ) AS ModifiedBy
>FROM
>    Users a
>WHERE
>    a.NumUser = 1
>
>Cheers
>Ken
>
>Microsoft MVP - Windows Server (IIS)
>
>  
>