mysql query repeating data with join

Question

This is my query.

 SELECT dr_trans_dtl.dr_ID, jo_trans_dtl.qty 
 FROM dr_trans_dtl 
 LEFT JOIN jo_trans_dtl ON dr_trans_dtl.jo_no = jo_trans_dtl.jo_no 
 WHERE dr_trans_dtl.dr_no = '3329' GROUP BY dr_trans_dtl.dr_ID

Here is the actual result:

What I wanted is the qty should be like this (500,40,1). Because that is the data in jo_trans_dtl.

GROUP BY could be GROUP BY dr_trans_dtl.dr_ID,jo_trans_dtl.qty — Sadikhasan
– Sadikhasan, Commented Dec 5, 2018 at 6:42
Give your expected output and give your current data that you are get. — Sadikhasan
– Sadikhasan, Commented Dec 5, 2018 at 6:56
Without seeing any sample data it's hard to be sure but you could probably remove the GROUP BY dr_trans_dtl.dr_ID and change SELECT dr_trans_dtl.dr_ID, jo_trans_dtl.qty to SELECT DISTINCT dr_trans_dtl.dr_ID, jo_trans_dtl.qty — Nick
– Nick, Commented Dec 5, 2018 at 6:58

Zaynul Abadin Tuhin · Accepted Answer · 2018-12-05 06:42:31Z

1

It seems you need aggregate function as you used group by

 SELECT dr_trans_dtl.dr_ID,sum(jo_trans_dtl.qty) as qty
 FROM dr_trans_dtl 
 JOIN jo_trans_dtl ON dr_trans_dtl.jo_no = jo_trans_dtl.jo_no 
 WHERE dr_trans_dtl.dr_no = '3329'
 GROUP BY dr_trans_dtl.dr_ID

answered Dec 5, 2018 at 6:42

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jonathan Over a year ago

The result is dr_ID | qty 9074 | 509 9075 | 509 9076 | 509 It should be dr_ID | qty 9074 | 500 9075 | 40 9076 | 1 Because (500,40,1) are in the jo_trans_dtl.

Zaynul Abadin Tuhin Over a year ago

@Jonathan share your both table data

Collectives™ on Stack Overflow

mysql query repeating data with join

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related