CODEWITHSUNDEEP
sqlasp.netsql-serverleft-join
radha singh
radha singh

Reputation: 71

Sql fetch data with join

Here there two table Transaction and Refundtransaction

I want to fetch the data using join and duplicate data should be fetch.

I am using this query

select tr.transactionid, tr.customerid, tr.custname,
       rt.Amount, rt.Refund_Amount, rt.transactionid,   rt.referenceid
from Transaction tr 
left join RefundTransaction rt on rt.referenceid = tr.transactionid

It is not giving proper data. It is not selecting duplicate data from refund data

Data and expected result

please help here i am inserting image from which you can get actual scenario.

Upvotes: 1

Views: 86

Answers (4)

Rohan uniyal
Rohan uniyal

Reputation: 21

This Could be simply solvedd using inner join between the two as per your image

SELECT t.transactionid, t.customerid, t.custname,
       rt.Amount, rt.Refund_Amount, rt.transactionid,   rt.referenceid
FROM TRANSACTION t
innerjoin [RefundTransaction ] rt ON rt.referenceid = t.transactionid

Upvotes: 1

Arulkumar
Arulkumar

Reputation: 13237

As per the image, you need to place the TRANSACTION table in the LEFT JOIN. Then only you can get all the entries from RefundTransaction table.

SELECT rt.transactionid
    ,tr.customerid
    ,tr.custname
    ,tr.Amount AS Refund_Amount
    ,rt.transactionid
    ,rt.referenceid
    ,rt.Amount 
FROM RefundTransaction rt
LEFT JOIN [TRANSACTION] tr ON rt.referenceid = tr.transactionid

Upvotes: 0

Dheeraj Kumar
Dheeraj Kumar

Reputation: 4175

You are doing excatly opposite for joins.

This should work.

select tr.transactionid, tr.customerid, tr.custname,
       rt.Amount, rt.Refund_Amount, rt.transactionid,   rt.referenceid
from RefundTransaction tr left join
     Transaction rt
     on rt.referenceid = tr.transactionid

Upvotes: 0

user6241578
user6241578

Reputation:

If you want to select all datas you have to make a full outer join 

select tr.transactionid,  tr.customerid,  tr.custname,    rt.Amount, rt.Refund_Amount,   rt.transactionid,   rt.referenceid from Transaction tr full outer join RefundTransaction rt on rt.referenceid = tr.transactionid

here you can find more info for joins

Upvotes: 0

Related Questions