I ran into a situation where I have two tables that store employee information, Table1 are the employees and table2 are 3rd party temps. These tables use a different convention for the IDs (not something I have control over).
The problem is that oftentimes these 3rd party temps become employed and there is no link between these tables. When this happens, I need to make sure they don't exists in Table2 before I create them. Right now the I just want to identify matches on DOB & Last 4, although I'm probably going to add at least first name to the criteria but right now I'd like to start somewhere.
The columns although name differently are the same (DOB = Birth Date, Code = Last 4)
CREATE TABLE Table1
([Emp_ID] int, [DOB] date, [code] varchar(10))
;
INSERT INTO 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 INTO 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 is what I came up with, It seems to work but I need to return the user_id from table2 that is producing this match?
SELECT *
FROM table1 t1
WHERE EXISTS (SELECT 1 FROM table2 t2 WHERE t1.DOB = t2.Birth_date)
AND EXISTS (SELECT 1 FROM table2 t2 WHERE t1.code = t2.last4)
Thanks!
Try this
Without JOINS:
SELECT t1.*,
(SELECT user_id FROM table2 t2
WHERE t1.DOB = t2.Birth_date and t1.code = t2.last4) user_id
FROM table1 t1
WHERE EXISTS (SELECT 1 FROM table2 t2
WHERE t1.DOB = t2.Birth_date and t1.code = t2.last4)
With JOINS
SELECT t1.*, t2.user_id
FROM table1 t1
inner join table2 t2 on t1.DOB = t2.Birth_date and t1.code = t2.last4