Join the ruins

Question

I have two tables that contain the people who work in the company and their employment data (so people are one table, employment is another). The table "People" contains information about where the person lives, emergency contact, phone number bla bla bla. The employment table contains information about where he works, the nearest boss and much more. These tables have been corrupted and now contain several duplicate claims. Both tables now have a Person identifier, but the busy identifier is only in busy. I need both numbers for all the people who have been duplicated.

This works great:

SELECT DISTINCT pp.Personid, pp.Firstname, pp.Lastname, pp.Address, FROM People pp JOIN People pp2 ON pp.Firstname = pp2.Firstname AND pp.Lastname = pp2.Lastname AND pp.Address = pp2.Address AND pp.Personid <> pp2.Personid ORDER BY pp.Firstname, pp.Lastname, pp.Personid 

returns the following values ​​(but does not include the Employment number, as you can see):

 1001 Carl Johnsson Bigstreet 1 1002 Carl Johnsson Bigstreet 1 1003 Carl Johnsson Bigstreet 1 1010 Andrew Wilkinsson Smallstreet 2 1011 Andrew Wilkinsson Smallstreet 2 

Now, to add an id of employment, I join this table as follows:

 SELECT DISTINCT pp.Personid, e.Employmentid, pp.Firstname, pp.Lastname, pp.Address, FROM People pp JOIN People pp2 ON pp.Firstname = pp2.Firstname AND pp.Lastname = pp2.Lastname AND pp.Address = pp2.Address AND pp.Personid <> pp2.Personid JOIN Employment e on pp.Personid = e.Personid ORDER BY pp.Firstname, pp.Lastname, pp.Personid 

And everything goes to h ** l in the basket with the following result:

 1001 1111 Carl Johnsson Bigstreet 1 1001 1111 Carl Johnsson Bigstreet 1 1001 1111 Carl Johnsson Bigstreet 1 1010 1234 Andrew Wilkinsson Smallstreet 2 1010 1234 Andrew Wilkinsson Smallstreet 2 

As you can see, I get both Personid and Employmentid, but now I only get one (repeated the correct number of times), so I don't have all the other Personid and Employmentid on my list.

Why?

What happened to my formation that broke the party?

Answer 1

Well, make some sample data;

 CREATE TABLE #People (PersonID int, FirstName varchar(50), LastName varchar(50), Address1 varchar(50)) INSERT INTO #People (PersonID, FirstName, LastName, Address1) VALUES ('1','Mike','Hunt','Cockburn Crescent') ,('2','Mike','Hunt','Cockburn Crescent') ,('3','Mike','Hunt','Cockburn Crescent') ,('4','Connie','Lingus','Dyke Close') ,('5','Connie','Lingus','Dyke Close') ,('6','Eric','Shun','Tickle Avenue') ,('7','Ivana','Humpalot','Bottom Street') CREATE TABLE #Employment (PersonID int, EmploymentID int) INSERT INTO #Employment (PersonID, EmploymentID) VALUES ('1','10') ,('2','11') ,('3','12') ,('4','13') ,('5','14') ,('6','15') ,('7','16') 

I would make the first query differently, if you duplicate duplicates in a subsample, it will be easier, you can connect to the employment table without any problems;

 SELECT pp.PersonID ,em.EmploymentID ,pp.FirstName ,pp.LastName ,pp.Address1 FROM #People pp JOIN ( SELECT FirstName ,LastName ,Address1 ,COUNT(1) records FROM #People GROUP BY FirstName ,LastName ,Address1 HAVING COUNT(1) > 1 ) pp2 ON pp.FirstName = pp2.FirstName AND pp.LastName = pp2.LastName AND pp.Address1 = pp2.Address1 LEFT JOIN #Employment em ON pp.PersonID = em.PersonID 

Remember to clear the temporary tables;

 DROP TABLE #People DROP TABLE #Employment 

Answer 2

I think you should try this.

 SELECT DISTINCT ep.Personid, ep.Employementid, ep.FirstName, ep.LastName, ep.Address FROM Person P join (SELECT pp.Personid, e.Employmentid, pp.Firstname, pp.Lastname, pp.Address, from PP JOIN Employment e on pp.Personid = e.Personid ) ep on P.Firstname = ep.Firstname AND P.Lastname = ep.Lastname AND P.Address = ep.Address AND P.Personid <> ep.Personid ORDER BY P.Firstname, P.Lastname, P.Personid 

Sir please check and answer me

Answer 3

Your code should work, and I cannot reproduce your problem using the data I compiled. The result you see tells me that in the employment table there are several person identifiers for carl johnsson and that the employment is different - although it looks the same at the output. Can you provide table definitions and sample data?