Повторяющиеся строки после нескольких соединений SQL

Я получаю много повторяющихся строк из этого запроса. Что-то не так с моими левыми соединениями? Я пробовал также присоединиться к inners, но они совсем не помогают. Я думаю, что проблема заключается в том, что если одно левое соединение верно, то это добавляет строку к результатам

SELECT DP.FirstName, DP.LastName, DECA.Email, UI.Id, ET.EmplId,
HPPTR.OrganizationUnitId, A.CountryRegionId, ET.EmplIdentNumber,
ET.JobId_INN, HPPTR.Description, ET.HRMResponsible_INN, A.Street, A.ZipCode,
A.City, HPER.ValidFromDateTime, HCJF.INN_JobAgreementType,
HPJTR.INN_EmpJobType, HPPTR.hrmemploymentfactor

FROM DirPartyTable AS DP

LEFT JOIN DIRPARTYECOMMUNICATIONRELA2608 AS DPECR ON DP.PartyId = DPECR.PartyId
LEFT JOIN DirECommunicationAddress AS DECA ON DPECR.ValuesRecId = DECA.RecId

LEFT JOIN EmplTable AS ET ON DP.PartyId = ET.PartyId
LEFT JOIN SYSCOMPANYUSERINFO AS SCUI ON ET.EmplId = SCUI.EmplId
LEFT JOIN USERINFO AS UI ON SCUI.UserId = UI.Id

LEFT JOIN HRPPARTYPOSITIONTABLERELAT2226 AS HPPTR ON ET.EmplId = HPPTR.Reference
LEFT JOIN HRPPartyJobTableRelationship AS HPJTR ON HPPTR.JobId = HPJTR.Jobid
LEFT JOIN HRMCompJobFunction AS HCJF ON HPJTR.HrmCompJobFunctionId = HCJF.JobFunctionId

LEFT JOIN Address AS A ON ET.RecId = A.AddrRecId

LEFT JOIN HRMPARTYEMPLOYEERELATIONSHIP AS HPER ON ET.EmplId = HPER.EmplId

WHERE DP.DataAreaId != 'cvc'
AND DECA.ECommunicationTypeId = 'email'
AND A.addrtableid='256'
AND UI.NETWORKALIAS IS NOT NULL
AND A.DataAreaId = DP.DataAreaId
AND SCUI.DataAreaId = DP.DataAreaId
AND ET.DataAreaId = DP.DataAreaId

ORDER BY ET.EmplId

Вот схема

FirstName   LastName    Email   Id  EmplId  OrganizationUnitId  CountryRegionId EmplIdentNumber CountryRegionId Description     Street  ZipCode City    ValidFromDateTime   INN_JobAgreementType    INN_EmpJobType  hrmemploymentfactor
John    Head    john.head@example.com   johnh   headjh  org1    FI  131564-234523   CHZ IT GUY      rockstreet  534523  Bigplace    2009-02-16 00:00:00.000 NULL        1.000000000000
John    Head    john.head@example.com   johnh   headjh  org2    FI  131564-234524   CHZ IT GUY      rockstreet  534523  Bigplace    2009-02-16 00:00:00.000 NULL        1.000000000000
John    Head    john.head@example.com   johnh   headjh  org3    FI  131564-234525   CHZ IT GUY      rockstreet  534523  Bigplace    2009-02-16 00:00:00.000 NULL        1.000000000000
John    Head    john.head@example.com   johnh   headjh  org4    FI  131564-234526   CHZ IT GUY      rockstreet  534523  Bigplace    2009-02-16 00:00:00.000 NULL        1.000000000000
John    Head    john.head@example.com   johnh   headjh  org5    FI  131564-234527   CHZ IT GUY      rockstreet  534523  Bigplace    2009-02-16 00:00:00.000 NULL        0.000000000000
John    Head    john.head@example.com   johnh   headjh  org1    FI  131564-234528   CHZ IT GUY      rockstreet  534523  Bigplace    2009-02-16 00:00:00.000 NULL        1.000000000000
John    Head    john.head@example.com   johnh   headjh  org2    FI  131564-234529   CHZ IT GUY      rockstreet  534523  Bigplace    2009-02-16 00:00:00.000 NULL        1.000000000000
John    Head    john.head@example.com   johnh   headjh  org3    FI  131564-234530   CHZ IT GUY      rockstreet  534523  Bigplace    2009-02-16 00:00:00.000 NULL        1.000000000000
John    Head    john.head@example.com   johnh   headjh  org4    FI  131564-234531   CHZ IT GUY      rockstreet  534523  Bigplace    2009-02-16 00:00:00.000 NULL        1.000000000000
John    Head    john.head@example.com   johnh   headjh  org5    FI  131564-234532   CHZ IT GUY      rockstreet  534523  Bigplace    2009-02-16 00:00:00.000 NULL        0.000000000000
John    Head    john.head@example.com   johnh   headjh  org1    FI  131564-234533   CHZ IT GUY      rockstreet  534523  Bigplace    2009-01-19 00:00:00.000 NULL        1.000000000000
John    Head    john.head@example.com   johnh   headjh  org2    FI  131564-234534   CHZ IT GUY      rockstreet  534523  Bigplace    2009-01-19 00:00:00.000 NULL        1.000000000000
John    Head    john.head@example.com   johnh   headjh  org3    FI  131564-234535   CHZ IT GUY      rockstreet  534523  Bigplace    2009-01-19 00:00:00.000 NULL        1.000000000000
John    Head    john.head@example.com   johnh   headjh  org4    FI  131564-234536   CHZ IT GUY      rockstreet  534523  Bigplace    2009-01-19 00:00:00.000 NULL        1.000000000000
John    Head    john.head@example.com   johnh   headjh  org5    FI  131564-234537   CHZ IT GUY      rockstreet  534523  Bigplace    2009-01-19 00:00:00.000 NULL        0.000000000000
John    Head    john.head@example.com   johnh   headjh  org1    FI  131564-234538   CHZ IT GUY      rockstreet  534523  Bigplace    2009-01-19 00:00:00.000 NULL        1.000000000000
John    Head    john.head@example.com   johnh   headjh  org2    FI  131564-234539   CHZ IT GUY      rockstreet  534523  Bigplace    2009-01-19 00:00:00.000 NULL        1.000000000000
John    Head    john.head@example.com   johnh   headjh  org3    FI  131564-234540   CHZ IT GUY      rockstreet  534523  Bigplace    2009-01-19 00:00:00.000 NULL        1.000000000000
John    Head    john.head@example.com   johnh   headjh  org4    FI  131564-234541   CHZ IT GUY      rockstreet  534523  Bigplace    2009-01-19 00:00:00.000 NULL        1.000000000000
John    Head    john.head@example.com   johnh   headjh  org5    FI  131564-234542   CHZ IT GUY      rockstreet  534523  Bigplace    2009-01-19 00:00:00.000 NULL        0.000000000000
John    Head    john.head@example.com   johnh   headjh  org1    FI  131564-234543   CHZ IT GUY      rockstreet  534523  Bigplace    2011-01-01 00:00:00.000 NULL        1.000000000000
John    Head    john.head@example.com   johnh   headjh  org2    FI  131564-234544   CHZ IT GUY      rockstreet  534523  Bigplace    2011-01-01 00:00:00.000 NULL        1.000000000000
John    Head    john.head@example.com   johnh   headjh  org3    FI  131564-234545   CHZ IT GUY      rockstreet  534523  Bigplace    2011-01-01 00:00:00.000 NULL        1.000000000000
John    Head    john.head@example.com   johnh   headjh  org4    FI  131564-234546   CHZ IT GUY      rockstreet  534523  Bigplace    2011-01-01 00:00:00.000 NULL        1.000000000000
John    Head    john.head@example.com   johnh   headjh  org5    FI  131564-234547   CHZ IT GUY      rockstreet  534523  Bigplace    2011-01-01 00:00:00.000 NULL        0.000000000000
John    Head    john.head@example.com   johnh   headjh  org1    FI  131564-234548   CHZ IT GUY      rockstreet  534523  Bigplace    2011-01-01 00:00:00.000 NULL        1.000000000000
John    Head    john.head@example.com   johnh   headjh  org2    FI  131564-234549   CHZ IT GUY      rockstreet  534523  Bigplace    2011-01-01 00:00:00.000 NULL        1.000000000000
John    Head    john.head@example.com   johnh   headjh  org3    FI  131564-234550   CHZ IT GUY      rockstreet  534523  Bigplace    2011-01-01 00:00:00.000 NULL        1.000000000000
John    Head    john.head@example.com   johnh   headjh  org4    FI  131564-234551   CHZ IT GUY      rockstreet  534523  Bigplace    2011-01-01 00:00:00.000 NULL        1.000000000000
John    Head    john.head@example.com   johnh   headjh  org5    FI  131564-234552   CHZ IT GUY      rockstreet  534523  Bigplace    2011-01-01 00:00:00.000 NULL        0.000000000000
John    Head    john.head@example.com   johnh   headjh  org1    FI  131564-234553   CHZ IT GUY      rockstreet  534523  Bigplace    2011-01-02 00:00:00.000 NULL        1.000000000000
John    Head    john.head@example.com   johnh   headjh  org2    FI  131564-234554   CHZ IT GUY      rockstreet  534523  Bigplace    2011-01-02 00:00:00.000 NULL        1.000000000000
John    Head    john.head@example.com   johnh   headjh  org3    FI  131564-234555   CHZ IT GUY      rockstreet  534523  Bigplace    2011-01-02 00:00:00.000 NULL        1.000000000000
John    Head    john.head@example.com   johnh   headjh  org4    FI  131564-234556   CHZ IT GUY      rockstreet  534523  Bigplace    2011-01-02 00:00:00.000 NULL        1.000000000000
John    Head    john.head@example.com   johnh   headjh  org5    FI  131564-234557   CHZ IT GUY      rockstreet  534523  Bigplace    2011-01-02 00:00:00.000 NULL        0.000000000000
John    Head    john.head@example.com   johnh   headjh  org1    FI  131564-234558   CHZ IT GUY      rockstreet  534523  Bigplace    2011-01-02 00:00:00.000 NULL        1.000000000000
John    Head    john.head@example.com   johnh   headjh  org2    FI  131564-234559   CHZ IT GUY      rockstreet  534523  Bigplace    2011-01-02 00:00:00.000 NULL        1.000000000000
John    Head    john.head@example.com   johnh   headjh  org3    FI  131564-234560   CHZ IT GUY      rockstreet  534523  Bigplace    2011-01-02 00:00:00.000 NULL        1.000000000000
John    Head    john.head@example.com   johnh   headjh  org4    FI  131564-234561   CHZ IT GUY      rockstreet  534523  Bigplace    2011-01-02 00:00:00.000 NULL        1.000000000000
John    Head    john.head@example.com   johnh   headjh  org5    FI  131564-234562   CHZ IT GUY      rockstreet  534523  Bigplace    2011-01-02 00:00:00.000 NULL        0.000000000000
John    Head    john.head@example.com   johnh   headjh  org1    FI  131564-234563   CHZ IT GUY      rockstreet  534523  Bigplace    2010-02-04 00:00:00.000 NULL        1.000000000000
John    Head    john.head@example.com   johnh   headjh  org2    FI  131564-234564   CHZ IT GUY      rockstreet  534523  Bigplace    2010-02-04 00:00:00.000 NULL        1.000000000000
John    Head    john.head@example.com   johnh   headjh  org3    FI  131564-234565   CHZ IT GUY      rockstreet  534523  Bigplace    2010-02-04 00:00:00.000 NULL        1.000000000000
John    Head    john.head@example.com   johnh   headjh  org4    FI  131564-234566   CHZ IT GUY      rockstreet  534523  Bigplace    2010-02-04 00:00:00.000 NULL        1.000000000000
John    Head    john.head@example.com   johnh   headjh  org5    FI  131564-234567   CHZ IT GUY      rockstreet  534523  Bigplace    2010-02-04 00:00:00.000 NULL        0.000000000000
John    Head    john.head@example.com   johnh   headjh  org1    FI  131564-234568   CHZ IT GUY      rockstreet  534523  Bigplace    2010-02-04 00:00:00.000 NULL        1.000000000000
John    Head    john.head@example.com   johnh   headjh  org2    FI  131564-234569   CHZ IT GUY      rockstreet  534523  Bigplace    2010-02-04 00:00:00.000 NULL        1.000000000000
John    Head    john.head@example.com   johnh   headjh  org3    FI  131564-234570   CHZ IT GUY      rockstreet  534523  Bigplace    2010-02-04 00:00:00.000 NULL        1.000000000000
John    Head    john.head@example.com   johnh   headjh  org4    FI  131564-234571   CHZ IT GUY      rockstreet  534523  Bigplace    2010-02-04 00:00:00.000 NULL        1.000000000000
John    Head    john.head@example.com   johnh   headjh  org5    FI  131564-234572   CHZ IT GUY      rockstreet  534523  Bigplace    2010-02-04 00:00:00.000 NULL        0.000000000000

1 ответ

Вам нужно подумать, является ли столбец справа правым или нет.

Например: в

FROM DirPartyTable AS DP
LEFT JOIN DIRPARTYECOMMUNICATIONRELA2608 AS DPECR ON DP.PartyId = DPECR.PartyId

Является DPECR.PartyID уникальным? Если это не так, то каждая строка в DP, которая отображается на несколько строк в DPECR, будет появляться несколько раз.

... и это продолжается для всех ваших других соединений. С синтаксисом все в порядке, но вы должны спросить себя об уникальности.

Чтобы выяснить, что происходит, попробуйте сделать SELECT 1 Вместо списка полей, начиная с первой таблицы, посмотрите, сколько строк возвращено. Затем попробуйте первые две или три таблицы, пока не сможете выяснить, какие объединения увеличивают количество возвращаемых строк. Это ваши проблемы и требуют дальнейшего изучения вашего стола.

Другие вопросы по тегам