Search for values in table1 that match values in table2 without joins (Sql Server 2008) -

i ran situation have 2 tables store employee information, table1 employees , table2 3rd party temps. these tables use different convention ids (not have control over).

the problem oftentimes these 3rd party temps become employed , there no link between these tables. when happens, need make sure don't exists in table2 before create them. right want identify matches on dob & last 4, although i'm going add @ least first name criteria right i'd start somewhere.

the columns although name differently same (dob = birth date, code = last 4)

create table table1  ([emp_id] int, [dob] date, [code] varchar(10)) ;  insert table1  ([emp_id], [dob], [code]) values  (55556, '1966-01-15', '5454'),  (55557, '1980-03-21', '6868'),  (55558, '1985-04-26', '7979'),  (55559, '1990-10-17', '1212'),  (55560, '1992-12-30', '6767') ;  create table table2  ([user_id] int, [birth_date] date, [last4] varchar(10)) ;  insert table2  ([user_id], [birth_date], [last4]) values  (22223, '1966-01-15', '5454'),  (22224, '1980-03-21', '6868'),  (22225, '1975-07-19', '4545'),  (22226, '1988-05-24', '3434') ; 

here came with, seems work need return user_id table2 producing match?

select * table1 t1  exists (select 1 table2 t2 t1.dob = t2.birth_date) , exists (select 1 table2 t2 t1.code = t2.last4) 

thanks!

try this

without joins:

select t1.*, (select user_id table2 t2                t1.dob = t2.birth_date , t1.code = t2.last4) user_id table1 t1  exists (select 1 table2 t2                t1.dob = t2.birth_date , t1.code = t2.last4) 

with joins

select t1.*, t2.user_id table1 t1  inner join table2 t2 on t1.dob = t2.birth_date , t1.code = t2.last4 

sql demo