CODEWITHSUNDEEP
sqloracle-databasejoin
Matt
Matt

Reputation: 21

sql is duplicating my results

I believe the problem is within my joins but i am unable to correct it. The SQL should return 3 rows however it is duplicating and returning 12 rows instead. Any help would be much appreciated!

SELECT J.JOURNEY_NUMBER,
  L.DESCRIPTION,
  L.USE_CODE,
  J.REAL_START_DATE,
  J.REAL_END_DATE,
  S.STOP_ID,
  SN.WRIN_ID,
  J.JOURNEY_ID
FROM PDA_STG.JOURNEY J,
  PDA_STG.RESTAURANT R,
  PDA_STG.LOCATION L,
  PDA_STG.SERIAL_NUMBER SN,
  PDA_STG.STOP S
WHERE J.JOURNEY_ID = R.JOURNEY_ID
AND l.loc_id       = r.rest_loc_id
AND J.JOURNEY_ID   = S.JOURNEY_ID
AND S.STOP_ID      = SN.STOP_ID
AND SN.WRIN_ID     = '00768669'
AND j.dc_loc_id = '994'
AND J.JOURNEY_ID = '357020'
AND J.PLANNED_START_DATE < '20-APR-17'
ORDER BY J.JOURNEY_ID DESC

Upvotes: 1

Views: 60

Answers (2)

Matt
Matt

Reputation: 21

Thanks for the feedback, i just done as India.Rocket said and it worked perfectly.

without sample it's difficult to tell what's wrong with the query. But if rows are exact duplicate then just put a distinct after select. That should do the job – India.Rocket 46 mins ago

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95072

You are probably joining records that you don't want to join for which you'd have to add some join criteria. (For instance if the serial number could change for a stop, i.e. you keep old serial numbers with a date, you'd only want the latest serial number, not all.)

In order to find the flaw in your query you can select * and see what records you are actually selecting.

Upvotes: 2

Related Questions