Forum Discussion

shemajuc's avatar
shemajuc
New Member
9 years ago

Many to relation error

How to solve below error

''Column 'jntkey' in Table 'vw_pmsjntd_summ' contains a duplicate value 'EP-146 048 MW065' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.''

 

 

My view

create  view [dbo].[vw_pmsjntd_summ]
as
select distinct d.welder,h.kp,h.kp_no,h.refkey,h.weldrepno,h.welddate,h.rt1,
case when h.rt1dt>='01/01/2015' then h.rt1dt else null end as rt1dt,
h.rtpassed,h.sortfld1,h.inchdia,h.stagno,h.etagno,wps,
d.refkey+d.welder as jntkey,
case when d.welddate>='01/01/2015' then 1 else 0 end as welded,
case when d.welddate>='01/01/2015' and rt1dt>='01/01/2015' then 1 else 0 end as tested,
case when d.welddate>='01/01/2015' and rt1dt>='01/01/2015' and rtpassed=1 then 1 else 0 end as accepted,
case when d.welddate>='01/01/2015' and rt1dt>='01/01/2015' and rtpassed=0 and sortfld1 like '%RS%' then 1 else 0 end as reshoot,
case when d.welddate>='01/01/2015' and rt1dt>='01/01/2015' and rtpassed=0 and sortfld1 not like '%RS%' then 1 else 0 end as rejected,
w.name,w.badgeno,cast(w.qualified as nvarchar(max)) as qualified,
case when reldate>='01/01/2015' then reldate else null end as reldate,
case when w.welder like 'C%' then 'AUTOMATIC' ELSE 'MANUAL' end AS welder_type
from vw_pmsjntd as d left join vw_pmsjnth as h on d.refkey=h.refkey
left join vw_pmswldr as w on d.welder=w.welder
where h.welddate>='01/01/2015' and h.weld_no not like '%R%' and h.weld_no not like '%CAS%' and h.isono not like 'MW%' AND ( H.ISONO <> 'EP-156'AND H.weld_no ='007' )

 

this is my relation

 

Error is below

 

''Column 'jntkey' in Table 'vw_pmsjntd_summ' contains a duplicate value 'EP-146 048 MW065' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.''

 

How to solve ?

7 Replies

  • vanessafvg's avatar
    vanessafvg
    Community Champion

    shemajuc you  need to have a  one to many relationship, obviously this is a many to many, so you either need to create a unique set of ids in a separte table and then link these 2 tables to the unique set of id's

    • shemajuc's avatar
      shemajuc
      New Member

      i make it

       

      then data not getting correct.....

      • vanessafvg's avatar
        vanessafvg
        Community Champion

        shemajuc did you remove duplicates in power query?

         

        2 issues could be happening here

         

        1. if you have any blank id's this will create an issue, you should replace all blanks with a dummy id like -1 or something

        2. power query is case sensitive, so if you removed duplicates in power query but the key has different cases like BM1 or bm1 power query will see this as different, make sure you change all to either upper or lower case