[thelist] SQL Question

[Paul Cowan]

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