Question: I have question regarding the performance factor for the following question.

Consider a table : Users,

```
 -----userId------- userName-----
        1              ABC
        2              XYZ

```

Another table : Transaction,

```
------createdBy----ReceivedBy----TransactionId
          1            2              50
          2            1              51

```

My required result is,

```
--------createdBy----ReceivedBy----TransactionId----userName_Creator-----userName_Receiver
            1            2               50            ABC                     XYZ
            2            1               51            XYZ                     ABC

```

I came out with two solutions,

1. Using a varied inner join,

> 
> SELECT \* FROM
>  Transaction T
>  INNER JOIN
>  (
>  SELECT createdBy,userName FROM Transaction
>  Inner Join
>  Users
> 
> 
> 
> ```
> )
> ON createdBy = userId
> INNER JOIN
> (
>   SELECT ReceivedBy,userName FROM Transaction
>   Inner Join
>   Users
> )   
> ON ReceivedBy= userId
> WHERE TransactionId = 51
> 
> ```
> 
> 

2.To maintain an hashmap for all the distinct userId and username and package the output by a look up in the data layer.

Which of these solution is optimal ? Also better technique is welcome.

--Siva

Answer: 
Your query is a good start, but it is unnecessarily complex. Try the following:

```
SELECT createdBy, ReceivedBy, TransactionId, userName
FROM Transaction
INNER JOIN Users ON createdBy = userId

```

Update: Doing it in the query is the better way (in my opinion), since it makes sense to get your data from the database the way you want to have it if possible. If you do (unnecessary) extra operations on your query result, your code will probably be harder to understand. To put it in a query is probably faster too, since your DBMS is optimized for this kind of operation.
why not simply doing something like:

```
select t.createdBy, t.ReceivedBy, t.TransactionId, u.userName
from Transactions u, Users u
where t.createdBy = u.userId

```