John,

>Hi,
>I have 3 tables as the following. I need to generate a report from this. How >does the SQL statment look like?

>TableName: Transaction
>Field
>TranNo Date Amount UserID
>0001 01/01/2000 200 000001
>0002 02/02/2000 100 000002

>TableName: ContraLog
>Field
>TranNo Date UserID
>0001 02/01/2000 000001
>0002 02/02/2000 000002

>TableName: User
>Field
>UserID UserName
>000001 John
>000002 Micheal

>How the sql statment look like if I need to generate the report look as the >following??

>Report
>Trans.TranNo Trans.Date Amount UserName1 Contra.Date UserName2

>UserName1 = user name who match with Trans.UserID
>UserName2 = user name who match with Trans.UserID

Try this. I am assuming you actually meant username2 joins to the contra.userid column.

SELECT Trans.TranNo, Trans.Date, Trans.Amount, User1.UserName As UserName1, Contra.Date, User2.UserName AS UserName2
FROM
Transaction AS Trans
LEFT JOIN ContraLog AS Contra ON Contra.TranNo = Trans.TranNo,
LEFT JOIN User As User1 ON Trans.UserId = User1.UserId,
LEFT JOIN User As User2 ON Contra.UserId = User2.UserId