[thelist] SQL Question
David Mitchell
dave at dbmdata.com
Wed Oct 29 20:30:47 CST 2003
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)
>
>
>
More information about the thelist
mailing list