Remove duplicate rows on multiple joins

Question

I have the following query

select m.movementid, m.orderid, m.pickupdate, m.pickupnotes, 
b.bin_size, b.bin_type, 
l.address, l.suburb, l.postcode, l.state, 
if(rs.run_action = 'Pick', if (r.run_state = 'Active' or r.run_state='Ready', 1, 0), 0) as active_on_pick
from bb_movement m
inner join bb_bins b on b.bin_id = m.bin_id
inner join bb_location l on l.locationid = m.locationid
inner join bb_runsheet rs on rs.movement_id = m.movementid
inner join bb_run r on r.run_id = rs.run_id
where m.mvtstate = 'Active'
order by m.locationid, m.pickupdate

which I want to produce a result where the active_on_pick column contains a 0 or 1 for each movementid. A record exists in the bb_runsheet table when a given movementid is added to a bb_run (a bb_run can have many bb_runsheet records, which primary contain a movementid)

The problem I have is that when a movement IS on a run (ie: has a bb_run record which matches the criteria and an entry in the bb_runsheet table) I get 2 rows in the result dataset - eg:

mvt_id active_on_pick
21     0
21     1

all I want is 21 and 1. I tried subqueries and other variants (such as group by movementid) but can seem to nail it. I'm using mysql

AgRizzo · Accepted Answer · 2013-10-05 13:47:00Z

1

I think if you move some of your logic into your join (changed to a LEFT JOIN), you can get the answer, moved more logic into another left join

select m.movementid, m.orderid, m.pickupdate, m.pickupnotes, 
b.bin_size, b.bin_type, 
l.address, l.suburb, l.postcode, l.state, 

IF(r.run_id IS NULL, 0, 1) as active_on_pick

from bb_movement m
inner join bb_bins b on b.bin_id = m.bin_id
inner join bb_location l on l.locationid = m.locationid

LEFT join bb_runsheet rs 
 ON rs.movement_id = m.movementid
    AND rs.run_action = 'Pick'

LEFT join bb_run r 
  ON r.run_id = rs.run_id
    AND (r.run_state = 'Active' or r.run_state='Ready')

where m.mvtstate = 'Active'
order by m.locationid, m.pickupdate

edited Oct 5, 2013 at 13:47

answered Oct 5, 2013 at 12:14

AgRizzo

5,2711 gold badge15 silver badges29 bronze badges

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

7 Comments

300baud Over a year ago

Thanks @Ag, I tried to run this query as is and get a 1241 error (running mysql 5.5.29). Removing the 1,0) from the last and removes the error but doesn't solve the problem. I'm not used to this syntax, but like the approach you've taken.. I'll keep hacking away...

AgRizzo Over a year ago

Sorry - there is a huge typo in it

AgRizzo Over a year ago

If you can, put some sample data in sqlfiddle.com. It would make testing easier

300baud Over a year ago

again thanks @Ag, runs, but I get the same results as original query. SQLFiddle? Never used that either!.. I'll take a look right now

300baud Over a year ago

sqlfiddle.com/#!2/46112/1 - I should add that I took out the superflous tables (bin / locations)

|

Low Chee Mun · Accepted Answer · 2013-10-05 10:42:05Z

0

you can use row_Number()

SELECT * FROM (    
select row_number() over (order by active_on_pick desc) rn,
m.movementid, m.orderid, m.pickupdate, m.pickupnotes, 
b.bin_size, b.bin_type, 
l.address, l.suburb, l.postcode, l.state, 
if(rs.run_action = 'Pick', if (r.run_state = 'Active' or r.run_state='Ready', 1, 0), 0) 
as active_on_pick
from bb_movement m
inner join bb_bins b on b.bin_id = m.bin_id
inner join bb_location l on l.locationid = m.locationid
inner join bb_runsheet rs on rs.movement_id = m.movementid
inner join bb_run r on r.run_id = rs.run_id
where m.mvtstate = 'Active'
order by m.locationid, m.pickupdate
) SampleTable
Where rn = 1

answered Oct 5, 2013 at 10:42

Low Chee Mun

6101 gold badge4 silver badges9 bronze badges

1 Comment

AgRizzo Over a year ago

row_number is on MSSQL, not MySQL

Collectives™ on Stack Overflow

Remove duplicate rows on multiple joins

2 Answers 2

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related