[thelist] SQL Question

[Ken Schaefer]

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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)