Get double records returned from SELECT because I failed to save the PK

Transact-SQL
1

I have two tables. A lookup table that has a PK and various columns. Looks something like this:

tblOperator
PK OperatorID, OperatorName, BadgeIDMy

main table saves the BadgeID value from the tblOperator instead of the PK OperatorID. So when I do an INNER JOIN between tblMain and tblOperator, I get duplicate records returned.

Is there a way to not get the duplicate records, other than to go back to myMain Table and save the FK OperatorID instead of the value of BadgeID.

Thanks,
Tim

2

sounds like the query is wrong but without DDL and sample data, it's hard to tell. Assuming one OperatorID has one BadgeID, then simply replacing the BadgeID with the OperatorID will return the same duplicate entries. I assume an Operator will have multiple records in the main table

3

hi

please see this link if it helps you

4

help us help you provide sample data. but I am assuming you might have duplicates in your operator table

use sqlteam
go

create table #tblOperator( OperatorID int identity(1,1), OperatorName nvarchar(150), BadgeID int);
create table #tblMain(id int, badgeid int, otherstuff varchar(150))

insert into #tblOperator
select 'Vader', 1 union
select 'Look Yo Fadda', 2

insert into #tblMain
select 1, BadgeID, 'fluffy'
from #tblOperator

select * 
  From #tblOperator o
  join #tblMain m on o.BadgeID = m.badgeid

drop table #tblOperator
drop table #tblMain

provide sample data as above. we don't have access to your sql server, this helps get a sample of your data