[thelist] SQL Question
Paul Cowan
evolt at funkwit.com
Wed Oct 29 17:30:18 CST 2003
David wrote:
> 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?
Something like this should work (MS SQL Server syntax, might need tweaking
for whatever DB you're using):
SELECT
Sometable.SomeID,
CreatedUser.FirstName AS CreatedFirstName,
CreatedUser.LastName AS CreatedLastName,
ModifiedUser.FirstName AS ModifiedFirstName,
ModifiedUser.LastName AS ModifiedLastName
FROM
SomeTable
INNER JOIN User AS CreatedUser ON
(SomeTable.CreatedBy = CreatedUser.NumUser)
LEFT JOIN User AS ModifiedUser ON
(SomeTable.ModifiedBy = ModifiedUser.NumUser)
Hope this helps,
Paul
More information about the thelist
mailing list