Join two tables. If the connected table has more than two matched records, only the first record is selected.

 

Table
Empid name
A01 Tom
A02 Mary
A03 Gary

Table B
Classid empid cname
C01 A01 English
C02 A01 math
C03 A02 physics

Expected results:
Empid classid name cname
A01 c01 Tom English
A02 C03 Mary physics
A03 null Gary null

 

Solution 1:

Declare @ T1 table (empid varchar (10), name varchar (10 ))
Insert @ T1 select 'a01', 'Tom'
Insert @ T1 select 'a02', 'Mary'
Insert @ T1 select 'a03', 'gary'

Declare @ T2 table (classid varchar (10), empid varchar (10), cname varchar (10 ))
Insert @ T2 select 'c01', 'a01', 'inc'
Insert @ T2 select '02', 'a01', 'Math'
Insert @ T2 select 'c03', 'a02', 'physics'

Select
A. empid, b1.classid, A. Name, b1.cname
From @ T1
Left join @ T2 B1
On a. empid = b1.empid and not exists (select 1 from @ T2 where empid = b1.empid and classid <b1.classid)

-- Result
Empid classid name cname
----------------------------------------
A01 c01 Tom English
A02 C03 Mary physics
A03 null Gary null

(The number of affected rows is 3)

Correct 2:

Declare @ T1 table (empid varchar (10), name varchar (10 ))
Insert @ T1 select 'a01', 'Tom'
Insert @ T1 select 'a02', 'Mary'
Insert @ T1 select 'a03', 'gary'

Declare @ T2 table (classid varchar (10), empid varchar (10), cname varchar (10 ))
Insert @ T2 select 'c01', 'a01', 'inc'
Insert @ T2 select '02', 'a01', 'Math'
Insert @ T2 select 'c03', 'a02', 'physics'
-- Select 1 from @ T2 TMP where empid = TMP. empid and classid <TMP. classid
-- Select * From @ T2 TMP where not exists (select 1 from @ T2 where empid = TMP. empid and classid <TMP. classid)

Select t1.empid, t2.classid, t1.name, t2.cname
From @ T1 T1
Left join (select * From @ T2 TMP where not exists (select 1 from @ T2 where empid = TMP. empid and classid <TMP. classid) T2
On t1.empid = t2.empid