none
Left join onto subquery causing duplicate rows RRS feed

  • Question

  • I have a table that contains duplicate rows when I join my subquery onto jodbom. For each JobNo there should be one row. I'm trying to avoid using another sub query. 


    SELECT R,
    Stack,
    rtrim(resin.fbompart) resinpn, 
    resin.fbommeas,
    rtrim(color.fbompart) colorpn, 
    color.fbommeas  
    
    FROM   
    (SELECT
     ROW_NUMBER() OVER (PARTITION BY jodbom.fbompart ORDER BY jomast.frel_dt,jomast.fschdprior,jomast.fjobno) R,   
    ROW_NUMBER() OVER (PARTITION BY jodrtg.fdescnum ORDER BY jodrtg.fdescnum,jomast.frel_dt,jomast.fschdprior,jomast.fjobno) Stack    
    
    FROM jodbom   
    INNER JOIN jomast ON jodbom.fjobno =  jomast.fjobno   
    INNER JOIN jodrtg ON jomast.fjobno = jodrtg.fjobno   
    INNER JOIN inmastx ON jodbom.fbompart = inmastx.fpartno   
    LEFT JOIN sorels ON jomast.fsono+jomast.fkey = sorels.fsono+sorels.finumber+sorels.frelease
    WHERE Left(jomast.fstatus,1) <> 'C' AND jomast.flisapl = 1 AND jodbom.fltooling = 1 AND jodrtg.fpro_id <> '09' AND flschedule=1   
    
    ) Q    
    left JOIN jodbom as resin ON Q.JobNo=resin.fjobno AND 'LBS'=resin.fbommeas   
    left JOIN jodbom as color ON resin.fjobno=color.fjobno AND 'LBS'=color.fbommeas AND resin.fbompart<>color.fbompart    
    
    WHERE R = 1



    • Edited by David9501 Monday, October 8, 2018 7:19 PM confusion on sql fiddle
    Monday, October 8, 2018 6:40 PM

All replies

  • select [fbompart]  , [fbommeas] 
    , [fbompart1] ,[fbommeas1] , [fjobno] 
     from (
       Select *
           , row_number() Over(Partition by fjobno order by fbompart) rn From [dbo].[jodbomfake]) t
    where rn=1

    Monday, October 8, 2018 7:16 PM
    Moderator
  • Would you please mind following forum netiquette, and publishing the DDL for your tables? What you did post seems to be violating all of the ISO 11179 naming rules, and many of these things make no sense. For example, "color" can't be a table name because colors are attributes of entities. And the fact that you had only one color this table is the second problem. I've been doing this for a few decades now, and I have the feeling that your data model is completely wrong. Would you like some real help or you want to keep writing the same bad code?

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, October 8, 2018 8:23 PM
  • Hi David9501,

     

    Per your description, You maybe perform joins through foreign keys, which link to primary keys, which must be unique, thereby eliminating the risk of join duplication.

     

    In your original script , you have used three times in inner join and two times in left join.

    It will cause duplication and misses. For more details you can refer to this article :

    https://alexpetralia.com/posts/2017/7/19/more-dangerous-subtleties-of-joins-in-sql

     

    If possible, please share us your table structure , your logic and some sample data along with your expected result. So that we’ll get a right direction and make some test. Hope it can help you.

     

    Best Regards,

    Rachel



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 9, 2018 7:09 AM
  • Would you please mind following forum netiquette, and publishing the DDL for your tables? What you did post seems to be violating all of the ISO 11179 naming rules, and many of these things make no sense. For example, "color" can't be a table name because colors are attributes of entities. And the fact that you had only one color this table is the second problem. I've been doing this for a few decades now, and I have the feeling that your data model is completely wrong. Would you like some real help or you want to keep writing the same bad code?

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL


    Wow, you're obnoxious.
    Tuesday, October 9, 2018 1:13 PM
  • You have to simplify your code
    Wednesday, October 10, 2018 9:12 AM
  • To avoid dups, seek 1 to 1 relationships. If one row of the left subquery/table matches only one row of right table/subquery, you won't see any dups.

    If a given subquery/table already has dups in itself (before the join has even started), you can typically perform a Distinct or Group By operation (as a subquery) to eliminate them. Then join to the now-unique subquery.

    Wednesday, October 10, 2018 9:04 PM