Join two tables with additional field in one table

Question

I would like to join together two tables with additional columns.

First table is for number of products despatched by product

** Table 1 - Despatches **      
Month   ProductID   No_despatched
Jan     abc          10
Jan     def          15
Jan     xyz          12

The second table is for the number of products returned by product, but also an additional column by return reason

** Table 2 - Returns **     
Month   ProductID   No_returned  Return_reason
Jan     abc          2             Too big
Jan     abc          3             Too small
Jan     xyz          1            Wrong colour

I would like to join the tables to show returns and despatched on the same row with the number of despatched being duplicated if there are multiple return reasons for the same product.

** Desired output **
Month   ProductID   No_despatched   No_returned   Return_reason
Jan     abc               10              2         Too big
Jan     abc               10              3         Too small
Jan     xyz               12              1         Wrong colour

Hope this makes sense...

Thanks in advance!

afk

One would think a question like this would be closed as a duplicate instead of generating answers from people with a lot of reputation — Joakim Danielson, Mar 04 '19 at 14:54

score 2 · Answer 1 · answered Mar 04 '19 at 14:51

2

This seems like a basic JOIN:

select r.month, r.productid, d.no_despathed, r.no_returned, r.return_reason
from returns r join
     despatches d
     on r.month = d.month and r.productid = d.productid;

The results don't seem particularly useful, because some products are missing (those with no returns). And the amounts are duplicated if there is more than one return record.

answered Mar 04 '19 at 14:51

Gordon Linoff

1,242,037
58
646
786

Thanks for your response and a good point you've made. What would you suggest to be the best way to calculate returns rate for example? I thought that maybe having number of despatches and returns in the same table would be best (assuming now I also include products that aren't returned) Is there a better way to approach this? – afk Mar 04 '19 at 15:21
1

@afk . . . This question has no mention of "returns rate" -- and it has several answers. Ask a *new* question with appropriate sample data and desired results, explain what "returns rate" means along the way. Do not edit this question, because you will invalidate the answers of people who tried to help you. – Gordon Linoff Mar 04 '19 at 15:26

score 0 · Answer 2 · answered Mar 04 '19 at 14:52

0

just use join

  select a.*,b.No_returned,.Return_reason from
   table1 join table2 on a.ProductID=b.ProductID 
                            and a.month=b.month

In case of duplicate you may use distinct

answered Mar 04 '19 at 14:52

Zaynul Abadin Tuhin

31,407
5
33
63

score 0 · Answer 3 · answered Mar 04 '19 at 14:54

Changing the order of clauses in your question produces the result.

with additional columns.

SELECT Table1.Month, Table1.ProductID, Table1.NoDespatched, Table2.NoReturned, Table2.ReturnReason

join two tables

FROM Table1 LEFT JOIN Table2 
  ON Table1.Month=Table2.Month AND Table1.ProductID=Table2.ProductID

We use a LEFT JOIN because, presumably a product can be dispatched without being returned, but nobody can return a product you didn't send out.

Join two tables with additional field in one table

3 Answers3