Sql fetch data with join

Question

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.

I think for the provided data ,your query will work fine. It will pick the duplicate refund transactions for the matching transactionID. Only change is in the select query tour first column should replace with rt.transactionid. — Unnikrishnan R, Aug 17 '16 at 14:24

score 1 · Answer 1 · answered Feb 18 '19 at 14:16

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

Arulkumar · Accepted Answer · 2016-08-17T13:43:13.937

0

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

edited Aug 17 '16 at 13:43

answered Aug 17 '16 at 13:35

Arulkumar

12,541
13
48
65

score 0 · Answer 3 · edited May 23 '17 at 12:16

0

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

edited May 23 '17 at 12:16

Community

1
1

answered Aug 17 '16 at 13:35

score 0 · Answer 4 · answered Aug 17 '16 at 13:39

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

Sql fetch data with join

4 Answers4