View of multiple tables. Need to remove "doubles" defined by 1 table

Ok, so this is what i'm stuck with.

Full size

SELECT     dbo.InstellingGegevens.INST_SUBTYPE, dbo.InstellingGegevens.INST_BRON, dbo.InstellingGegevens.INST_INSTELLINGSNUMMER, 
                      dbo.InstellingGegevens.INST_NAAM, dbo.InstellingGegevens.INST_KORTENAAM, dbo.InstellingGegevens.INST_VESTIGINGSNAAM, 
                      dbo.InstellingGegevens.INST_ROEPNAAM, dbo.InstellingGegevens.INST_STATUUT, dbo.InstellingGegevens.ONDERWIJSNIVEAU_REF, 
                      dbo.InstellingGegevens.ONDERWIJSSOORT_REF, dbo.InstellingGegevens.DATUM_TOT, dbo.InstellingGegevens.DATUM_VAN, 
                      dbo.InstellingGegevens.VERBOND_REF, dbo.InstellingGegevens.VSKO_LID, dbo.InstellingGegevens.NET_REF, dbo.Instellingen.Inst_ID, dbo.Instellingen.INST_TYPE, 
                      dbo.Instellingen.INST_REF, dbo.Instellingen.INST_LOC_REF, dbo.Instellingen.INST_LOCNR, dbo.Instellingen.Opt_KalStandaard, dbo.InstellingTelecom.INST_TEL, 
                      dbo.InstellingTelecom.INST_FAX, dbo.InstellingTelecom.INST_EMAIL, dbo.InstellingTelecom.INST_WEB, dbo.InstellingAdressen.SOORT, 
                      dbo.InstellingAdressen.STRAAT, dbo.InstellingAdressen.POSTCODE, dbo.InstellingAdressen.GEMEENTE, dbo.InstellingAdressen.GEM_REF, 
                      dbo.InstellingAdressen.FUSIEGEM_REF, dbo.InstellingAdressen.FUSIEGEM, dbo.InstellingAdressen.ALFA_G, dbo.InstellingAdressen.PROVINCIE, 
                      dbo.InstellingAdressen.BISDOM, dbo.InstellingAdressen.ARRONDISSEMENT, dbo.InstellingAdressen.GEWEST, dbo.InstellingLogin.Inst_Gebruikersnaam, 
                      dbo.InstellingLogin.Inst_Concode, dbo.InstellingLogin.Inst_DirCode, dbo.InstellingLogin.DOSSNR, dbo.InstellingLogin.Instelling_ID, 
                      dbo.InstellingContPersDirecteurs.AANSPREKING, dbo.InstellingContPersDirecteurs.CONTACTPERSOON, dbo.InstellingContPersDirecteurs.FUNCTIE
FROM         dbo.InstellingGegevens RIGHT OUTER JOIN
                      dbo.Instellingen ON dbo.InstellingGegevens.INST_TYPE = dbo.Instellingen.INST_TYPE AND dbo.InstellingGegevens.INST_REF = dbo.Instellingen.INST_REF AND 
                      dbo.InstellingGegevens.INST_LOC_REF = dbo.Instellingen.INST_LOC_REF AND 
                      dbo.InstellingGegevens.INST_LOCNR = dbo.Instellingen.INST_LOCNR LEFT OUTER JOIN
                      dbo.InstellingTelecom ON dbo.InstellingGegevens.INST_TYPE = dbo.InstellingTelecom.INST_TYPE AND 
                      dbo.InstellingGegevens.INST_REF = dbo.InstellingTelecom.INST_REF AND 
                      dbo.InstellingGegevens.INST_LOC_REF = dbo.InstellingTelecom.INST_LOC_REF LEFT OUTER JOIN
                      dbo.InstellingAdressen ON dbo.InstellingGegevens.INST_TYPE = dbo.InstellingAdressen.INST_TYPE AND 
                      dbo.InstellingGegevens.INST_REF = dbo.InstellingAdressen.INST_REF AND 
                      dbo.InstellingGegevens.INST_LOC_REF = dbo.InstellingAdressen.INST_LOC_REF LEFT OUTER JOIN
                      dbo.InstellingLogin ON dbo.InstellingLogin.Inst_InstellingIKONType = dbo.Instellingen.INST_TYPE AND 
                      dbo.InstellingLogin.Inst_Ins开发者_JAVA技巧tellingIKON_REF = dbo.Instellingen.INST_REF AND dbo.InstellingLogin.Inst_Loc_REF = dbo.Instellingen.INST_LOC_REF AND 
                      dbo.InstellingLogin.Inst_Loc_Nr = dbo.Instellingen.INST_LOCNR LEFT OUTER JOIN
                      dbo.InstellingContPersDirecteurs ON dbo.InstellingGegevens.INST_TYPE = dbo.InstellingContPersDirecteurs.INST_TYPE AND 
                      dbo.InstellingGegevens.INST_REF = dbo.InstellingContPersDirecteurs.INST_REF AND 
                      dbo.InstellingGegevens.INST_LOC_REF = dbo.InstellingContPersDirecteurs.INST_LOC_REF
WHERE     (NOT (dbo.InstellingLogin.Inst_InstellingIKON_REF IS NULL))

So here is the problem:

the 'should be' PK is a 1 varchar 3 int's key. for every key there is supposed to be 1 row in each of the tables which you can see in the image. the 'parent' of those keys is the table Instellingen. This table is generated with a distinct select of InstellingenLogin

the real problem is that there are about 10 doubles in InstellingenLogin (of about 5k records) and because of this, some rows return double in the view, with only the columns of InstellingLogin different.

what i want is that if there are 2 or more rows in InstellingLogin with the same key, that only 1 will show (the first one,... doenst matter which one, just 1 will do).

in short that means that for every record in Instellingen i want 1 record in this view.

is there any way to do that?

---

I'm a bit confused but I think the answer below should illustrate how to acheive what you need:

SELECT * FROM Instellingen as i
CROSS APPLY
(   
    SELECT TOP (1) * FROM InstellingLogin as il
    WHERE i.INST_LOC_REF = il.Inst_Loc_REF 
    and i.INST_LOCNR=il.Inst_Loc_Nr 
    and i.INST_REF=il.Inst_InstellingIKON_REF 
    and i.INST_TYPE=il.Inst_InstellingIKONType
    order by il.Datum_tot
) la

This will basically join on Instellingen and InstellingenLogin but only on the first record found

---

You can use DENSE_RANK to Arbitrarily select a row. Basically create a CTE that ranks the instellinglogin and then only select the one with a Rank of 1. The tricky bit is that you have a left join to instellinglogin so you'll need to do the inner join inside the left join like so. Also I kept the Join condition as the the columns to partition. This may not be correct.

LEFT OUTER JOIN (dbo.instellinglogin 
                            INNER JOIN unique_login 
                              ON dbo.instellinglogin.inst_instellingikontype = 
                                      unique_login .inst_instellingikontype 
                                 AND dbo.instellinglogin.inst_instellingikon_ref = 
                                     unique_login.inst_instellingikon_ref 
                                 AND dbo.instellinglogin.inst_loc_ref = 
                                     unique_login.inst_loc_ref 
                                 AND dbo.instellinglogin.inst_loc_nr = 
                                     unique_login.inst_loc_nr 
                                 AND unique_login.therank = 1 ) 
             ON dbo.instellinglogin.inst_instellingikontype = 
                dbo.instellingen.inst_type 
                AND dbo.instellinglogin.inst_instellingikon_ref = 
                    dbo.instellingen.inst_ref 
                AND dbo.instellinglogin.inst_loc_ref = dbo.instellingen.inst_loc_ref 
                AND dbo.instellinglogin.inst_loc_nr = dbo.instellingen.inst_locnr 

Here's the complete SQL Below

WITH unique_login 
     AS (SELECT instellinglogin.inst_instellingikontype, 
                instellinglogin.inst_instellingikon_ref, 
                instellinglogin.inst_loc_ref, 
                instellinglogin.inst_loc_nr, 
                Dense_rank() OVER (ORDER BY 
                instellinglogin.inst_instellingikontype, 
                instellinglogin.inst_instellingikon_ref, 
                instellinglogin.inst_loc_ref, 
                instellinglogin.inst_loc_nr) AS therank) 
SELECT dbo.instellinggegevens.inst_subtype, 
       dbo.instellinggegevens.inst_bron, 
       dbo.instellinggegevens.inst_instellingsnummer, 
       dbo.instellinggegevens.inst_naam, 
       dbo.instellinggegevens.inst_kortenaam, 
       dbo.instellinggegevens.inst_vestigingsnaam, 
       dbo.instellinggegevens.inst_roepnaam, 
       dbo.instellinggegevens.inst_statuut, 
       dbo.instellinggegevens.onderwijsniveau_ref, 
       dbo.instellinggegevens.onderwijssoort_ref, 
       dbo.instellinggegevens.datum_tot, 
       dbo.instellinggegevens.datum_van, 
       dbo.instellinggegevens.verbond_ref, 
       dbo.instellinggegevens.vsko_lid, 
       dbo.instellinggegevens.net_ref, 
       dbo.instellingen.inst_id, 
       dbo.instellingen.inst_type, 
       dbo.instellingen.inst_ref, 
       dbo.instellingen.inst_loc_ref, 
       dbo.instellingen.inst_locnr, 
       dbo.instellingen.opt_kalstandaard, 
       dbo.instellingtelecom.inst_tel, 
       dbo.instellingtelecom.inst_fax, 
       dbo.instellingtelecom.inst_email, 
       dbo.instellingtelecom.inst_web, 
       dbo.instellingadressen.soort, 
       dbo.instellingadressen.straat, 
       dbo.instellingadressen.postcode, 
       dbo.instellingadressen.gemeente, 
       dbo.instellingadressen.gem_ref, 
       dbo.instellingadressen.fusiegem_ref, 
       dbo.instellingadressen.fusiegem, 
       dbo.instellingadressen.alfa_g, 
       dbo.instellingadressen.provincie, 
       dbo.instellingadressen.bisdom, 
       dbo.instellingadressen.arrondissement, 
       dbo.instellingadressen.gewest, 
       dbo.instellinglogin.inst_gebruikersnaam, 
       dbo.instellinglogin.inst_concode, 
       dbo.instellinglogin.inst_dircode, 
       dbo.instellinglogin.dossnr, 
       dbo.instellinglogin.instelling_id, 
       dbo.instellingcontpersdirecteurs.aanspreking, 
       dbo.instellingcontpersdirecteurs.contactpersoon, 
       dbo.instellingcontpersdirecteurs.functie 
FROM   dbo.instellinggegevens 
       RIGHT OUTER JOIN dbo.instellingen 
         ON dbo.instellinggegevens.inst_type = dbo.instellingen.inst_type 
            AND dbo.instellinggegevens.inst_ref = dbo.instellingen.inst_ref 
            AND dbo.instellinggegevens.inst_loc_ref = 
                dbo.instellingen.inst_loc_ref 
            AND dbo.instellinggegevens.inst_locnr = dbo.instellingen.inst_locnr 
       LEFT OUTER JOIN dbo.instellingtelecom 
         ON dbo.instellinggegevens.inst_type = dbo.instellingtelecom.inst_type 
            AND dbo.instellinggegevens.inst_ref = dbo.instellingtelecom.inst_ref 
            AND dbo.instellinggegevens.inst_loc_ref = 
                dbo.instellingtelecom.inst_loc_ref 
       LEFT OUTER JOIN dbo.instellingadressen 
         ON dbo.instellinggegevens.inst_type = dbo.instellingadressen.inst_type 
            AND dbo.instellinggegevens.inst_ref = 
                dbo.instellingadressen.inst_ref 
            AND dbo.instellinggegevens.inst_loc_ref = 
                dbo.instellingadressen.inst_loc_ref 
       LEFT OUTER JOIN (dbo.instellinglogin 
                        INNER JOIN unique_login 
                          ON dbo.instellinglogin.inst_instellingikontype = 
                                  unique_login .inst_instellingikontype 
                             AND dbo.instellinglogin.inst_instellingikon_ref = 
                                 unique_login.inst_instellingikon_ref 
                             AND dbo.instellinglogin.inst_loc_ref = 
                                 unique_login.inst_loc_ref 
                             AND dbo.instellinglogin.inst_loc_nr = 
                                 unique_login.inst_loc_nr 
                             AND unique_login.therank = 1 ) 
         ON dbo.instellinglogin.inst_instellingikontype = 
            dbo.instellingen.inst_type 
            AND dbo.instellinglogin.inst_instellingikon_ref = 
                dbo.instellingen.inst_ref 
            AND dbo.instellinglogin.inst_loc_ref = dbo.instellingen.inst_loc_ref 
            AND dbo.instellinglogin.inst_loc_nr = dbo.instellingen.inst_locnr 
       LEFT OUTER JOIN dbo.instellingcontpersdirecteurs 
         ON dbo.instellinggegevens.inst_type = 
            dbo.instellingcontpersdirecteurs.inst_type 
            AND dbo.instellinggegevens.inst_ref = 
                dbo.instellingcontpersdirecteurs.inst_ref 
            AND dbo.instellinggegevens.inst_loc_ref = 
                dbo.instellingcontpersdirecteurs.inst_loc_ref 
WHERE  ( NOT ( dbo.instellinglogin.inst_instellingikon_ref IS NULL ) ) 

---

You need to wrap InstellingenLogin in some kind of aggregate to remove the duplicates and then join to the result - you can do this with a subquery. You could do a GROUP BY with MIN() or MAX() to pick a value or you could do ROW_NUMBER() OVER (ORDER BY some_criteria PARTIION BY your_key) and pick the first row.

Also, I recommend using aliases on your tables - makes it far more readable

WITH UniqueLogins AS (
    -- How to pick Inst_Gebruikersnaam, Inst_Concode, Inst_DirCode, DOSSNR, Instelling_ID, InstellingIKONType, Inst_Loc_REF, Inst_Loc_Nr, Inst_InstellingIKON_REF
    SELECT key columns, MIN(non key column), MIN(non key column), MIN(non key column)
    FROM dbo.InstellingLogin
    GROUP BY key columns
)
SELECT     G.INST_SUBTYPE, G.INST_BRON, G.INST_INSTELLINGSNUMMER, 
                  G.INST_NAAM, G.INST_KORTENAAM, G.INST_VESTIGINGSNAAM, 
                  G.INST_ROEPNAAM, G.INST_STATUUT, G.ONDERWIJSNIVEAU_REF, 
                  G.ONDERWIJSSOORT_REF, G.DATUM_TOT, G.DATUM_VAN, 
                  G.VERBOND_REF, G.VSKO_LID, G.NET_REF, I.Inst_ID, I.INST_TYPE, 
                  I.INST_REF, I.INST_LOC_REF, I.INST_LOCNR, I.Opt_KalStandaard, T.INST_TEL, 
                  T.INST_FAX, T.INST_EMAIL, T.INST_WEB, A.SOORT, 
                  A.STRAAT, A.POSTCODE, A.GEMEENTE, A.GEM_REF, 
                  A.FUSIEGEM_REF, A.FUSIEGEM, A.ALFA_G, A.PROVINCIE, 
                  A.BISDOM, A.ARRONDISSEMENT, A.GEWEST, UniqueLogins.Inst_Gebruikersnaam, 
                  UniqueLogins.Inst_Concode, UniqueLogins.Inst_DirCode, UniqueLogins.DOSSNR, UniqueLogins.Instelling_ID, 
                  CPD.AANSPREKING, CPD.CONTACTPERSOON, CPD.FUNCTIE
FROM         dbo.InstellingGegevens AS G RIGHT OUTER JOIN
                  dbo.Instellingen AS I ON G.INST_TYPE = I.INST_TYPE AND G.INST_REF = I.INST_REF AND 
                  G.INST_LOC_REF = I.INST_LOC_REF AND 
                  G.INST_LOCNR = I.INST_LOCNR LEFT OUTER JOIN
                  dbo.InstellingTelecom AS T ON G.INST_TYPE = T.INST_TYPE AND 
                  G.INST_REF = T.INST_REF AND 
                  G.INST_LOC_REF = T.INST_LOC_REF LEFT OUTER JOIN
                  dbo.InstellingAdressen AS A ON G.INST_TYPE = A.INST_TYPE AND 
                  G.INST_REF = A.INST_REF AND 
                  G.INST_LOC_REF = A.INST_LOC_REF LEFT OUTER JOIN
                  UniqueLogins ON UniqueLogins.Inst_InstellingIKONType = I.INST_TYPE AND 
                  UniqueLogins.Inst_InstellingIKON_REF = I.INST_REF AND UniqueLogins.Inst_Loc_REF = I.INST_LOC_REF AND 
                  UniqueLogins.Inst_Loc_Nr = I.INST_LOCNR LEFT OUTER JOIN
                  dbo.InstellingContPersDirecteurs AS CPD ON G.INST_TYPE = CPD.INST_TYPE AND 
                  G.INST_REF = CPD.INST_REF AND 
                  G.INST_LOC_REF = CPD.INST_LOC_REF
WHERE     (NOT (UniqueLogins.Inst_InstellingIKON_REF IS NULL))

---

Or you could substitute the IntellingLogin table in the query with a derived table such as:

(SELECT RN = row_number() over
(partition by INST_LOC_REF,
INST_LOCNR, INST_REF, INST_TYPE order
by Datum_tot) , *  From
InstellingLogin) A

and add in the join condition: A.RN = 1