declare @t1 table
(rid varchar(20), cout varchar(10),seq tinyint);
insert into @t1 values('_r00000007','SG',3);
insert into @t1 values('_r00000007','KL',1);
insert into @t1 values('_r00000007','JB',2);
insert into @t1 values('_r00000008','SG',1);
insert into @t1 values('_r00000008','BI',2);
insert into @t1 values('_r00000008','KL',3);
insert into @t1 values('_r00000010','JB',1);
insert into @t1 values('_r00000010','KL',2);
/*@t1(rid,cout) is unique*/

declare @t2 table
(tickNo varchar(30),rid varchar(20), scout varchar(10), ecout varchar(10));
insert into @t2 values('SG1003000145','_r00000008','SG','KL');
insert into @t2 values('SG1003000146','_r00000008','SG','KL');
insert into @t2 values('JB1003000147','_r00000010','JB','KL');
/*@t2(tickNo) is unique*/

/*
@t2(rid) is a foreign key to relate to @t1
*/

tickNo       |  rid          |  scout | ecout  | scoutSeq  | ecoutSeq
---------------------------------------------------------------------
SG1003000145	_r00000008	SG	KL       1           3
SG1003000146	_r00000008	SG	KL       1           3
JB1003000147	_r00000008	JB	KL       1           2