Return Unique set with “DISTINCT” and multiple oth

I am creating a view from multiple tables. Each table being included has a column named Contact_ID. I am using MS SQL 2005. I see that I can use DISTINCT to return a unique set by Contact_ID but then my view only shows the Contact_ID.

I need the view to contain more than just the Contact_ID so I need to create a SELECT statement with all the correct columns but I can't have duplicate records.

Here is what I currently have: (This returns all the data I need but contains records with duplicates of Contact_ID)

SELECT dbo.[1_MAIN - Contacts].Contact_ID, dbo.[1_MAIN - Contacts].Date_entered_into_Database, dbo.[1_MAIN - Contacts].Date_of_Initial_Contact, 
         dbo.[1_MAIN - Contacts].[Company_ Name], dbo.[1_MAIN - Contacts].Key_Contact_Title, dbo.[1_MAIN - Contacts].Key_Contact_First_Name, 
         dbo.[1_MAIN - Contacts].Key_Contact_Middle, dbo.[1_MAIN - Contacts].Key_Contact_Last_Name, dbo.[1_MAIN - Contacts].Key_Credential, 
         dbo.[1_MAIN - Contacts].Key_Contact_Occupation, dbo.[1_MAIN - Contacts].Key_Degree_1, dbo.[1_MAIN - Contacts].Key_Degree_2, 
         dbo.[1_MAIN - Contacts].Key_Degree_3, dbo.[1_MAIN - Contacts].Date_of_Highest_Degree, dbo.[1_MAIN - Contacts].Work_Setting, 
         dbo.[1_MAIN - Contacts].Website_Address, dbo.[1_MAIN - Contacts].Email_1_Key_Contact, dbo.[1_MAIN - Contacts].Email_2, 
         dbo.[1_MAIN - Contacts].Email_3, dbo.[1_MAIN - Contacts].Day_Time_Phone_Number, dbo.[1_MAIN - Contacts].Extension, 
         dbo.[1_MAIN - Contacts].Mobile_Phone_Number, dbo.[1_MAIN - Contacts].Bus_Fax_Number, dbo.[1_MAIN - Contacts].Home_Phone_Number, 
         dbo.[1_MAIN - Contacts].Home_Fax_Number, dbo.[1_MAIN - Contacts].Mailing_Street_1, dbo.[1_MAIN - Contacts].Mailing_Street_2, 
         dbo.[1_MAIN - Contacts].Mailing_City, dbo.[1_MAIN - Contacts].Mailing_State, dbo.[1_MAIN - Contacts].[Mailing_Zip/Postal], 
         dbo.[1_MAIN - Contacts].Mailing_Country, dbo.[1_MAIN - Contacts].[Bad_Address?], dbo.[1_MAIN - Contacts].[PROV/REG?], 
         dbo.[1_MAIN - Contacts].status_flag, dbo.[1_MAIN - Contacts].status_flag AS status_flag2, dbo.Providers.Contact_ID AS Expr1, 
         dbo.Providers.Referral_Source, dbo.Resource_Center.access, dbo.Referral.Contact_Source, dbo.Resource_Center.cert_start_date, 
         dbo.Resource_Center.cert_exp_date, dbo.prov_training_records.Contact_ID AS Expr2, dbo.prov_training_records.date_reg_email_sent
FROM   dbo.[1_MAIN - Contacts] INNER JOIN
         dbo.Referral ON dbo.[1_MAIN - Contacts].Contact_ID = dbo.Referral.Referral_ID INNER JOIN
         dbo.prov_training_records ON dbo.[1_MAIN - Contacts].Contact_ID = dbo.prov_training_records.Contact_ID LEFT OUTER JOIN
         dbo.Resource_Center ON dbo.[1_MAIN - Contacts].Contact_ID = dbo.Resource_Center.Contact_ID FULL OUTER JOIN
         dbo.Providers ON dbo.[1_MAIN - Contacts].Contact_ID = dbo.Providers.Contact_ID

Same query with table names aliased:

SELECT c.Contact_ID, c.Date_entered_into_Database, c.Date_of_Initial_Contact, 
       c.[Company_ Name], c.Key_Contact_Title, c.Key_Contact_First_Name, 
       c.Key_Contact_Middle, c.Key_Contact_Last_Name, c.Key_Credential, 
       c.Key_Contact_Occupation, c.Key_Degree_1, c.Key_Degree_2, 
       c.Key_Degree_3, c.Date_of_Highest_Degree, c.Work_Setting, 
       c.Website_Address, c.Email_1_Key_Contact, c.Email_2, 
       c.Email_3, c.Day_Time_Phone_Number, c.Extension, 
       c.Mobile_Phone_Number, c.Bus_Fax_Number, c.Home_Phone_Number, 
       c.Home_Fax_Number, c.Mailing_Street_1, c.Mailing_Street_2, 
       c.Mailing_City, c.Mailing_State, c.[Mailing_Zip/Postal], 
       c.Mailing_Country, c.[Bad_Address?], c.[PROV/REG?], 
       c.status_flag, c.status_flag AS status_flag2, p.Contact_ID AS Expr1, 
       p.Referral_Source, rc.access, r.Contact_Source, rc.cert_start_date, 
       rc.cert_exp_date, tr.Contact_ID AS Expr2, tr.date_reg_email_sent
FROM   dbo.[1_MAIN - Contacts] c INNER JOIN
       dbo.Referral r ON c.Contact_ID = r.Referral_ID INNER JOIN
       dbo.prov_training_records tr ON c.Contact_ID = tr.Contact_ID LEFT OUTER JOIN
       dbo.Resource_Center rc ON c.Contact_ID = rc.Contact_ID FULL OUTER JOIN
       dbo.Providers p ON c.Contact_ID = p.Contact_ID

回答1:

WITH    q AS
        (
        SELECT  *,
                ROW_NUMBER() OVER (PARTITION BY dbo.[1_MAIN - Contacts].Contact_ID ORDER BY dbo.[1_MAIN - Contacts].Contact_ID) AS rn
        FROM    dbo.[1_MAIN - Contacts]
        INNER JOIN
                dbo.Referral
        ON      dbo.[1_MAIN - Contacts].Contact_ID = dbo.Referral.Referral_ID
        INNER JOIN
                dbo.prov_training_records
        ON      dbo.[1_MAIN - Contacts].Contact_ID = dbo.prov_training_records.Contact_ID
        LEFT OUTER JOIN
                dbo.Resource_Center
        ON      dbo.[1_MAIN - Contacts].Contact_ID = dbo.Resource_Center.Contact_ID
        FULL OUTER JOIN
                dbo.Providers
        ON      dbo.[1_MAIN - Contacts].Contact_ID = dbo.Providers.Contact_ID
        )
SELECT  *
FROM    q
WHERE   rn = 1

回答2:

Initially misread the question, you will need to group all the data but if you want to show all the data and there are uniques, you need to use min or max in some cases, if someone has 2 phones...what do you want to show?