CODEWITHSUNDEEP
javamysqlsqldatabasejoin
Siva Natarajan
Siva Natarajan

Reputation: 1429

Database Multiple Inner Join

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

Upvotes: 1

Views: 252

Answers (3)

jmail
jmail

Reputation: 6134

use this format:

SELECT a.batsman_id,a.matchs,a.hundreds,b.country_type, c.player_name
FROM batsman a  
            INNER JOIN countrycode b ON a.country_code=b.country_code
        INNER JOIN players c ON a.player_id=c.player_id
        ORDER BY a.batsman_id;

Upvotes: 0

Patrick Kostjens
Patrick Kostjens

Reputation: 5105

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.

Upvotes: 1

Xavier Coulon
Xavier Coulon

Reputation: 1600

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

Upvotes: 0

Related Questions