remove duplicate rows when joining 2 tables

Question

I have 2 tables: patient_allergens

prob_person_id  allergens
--------------  ---------
123             NO KNOWN ALLERGIES

and patient_medication

med_person_id   med_start_date  medication_name
-------------   --------------  ---------------
123             20140819        Seroquel 50 mg tablet
123             20140819        trazodone 50 mg tablet
123             20140825        clonazepam 2 mg tablet
123             20150407        duloxetine 60 mg capsule,delayed release
123             20160222        atorvastatin 40 mg tablet

When I join the tables I get:

select pa.prob_person_id, 
       pa.allergens, 
       pm.med_person_id, 
       pm.med_start_date, 
       pm.medication_name 
from patient_allergens pa 
left join patient_medication pm on pa.prob_person_id = pm.med_person_id                 


prob_person_id  allergens            med_person_id                          start_date  medication_name
--------------  ---------            -------------                          ----------  ---------------
123             NO KNOWN ALLERGIES  AF469E7B-3115-4715-8561-8088192D8AEE    20140819    Seroquel 50 mg tablet
123             NO KNOWN ALLERGIES  AF469E7B-3115-4715-8561-8088192D8AEE    20140819    trazodone 50 mg tablet
123             NO KNOWN ALLERGIES  AF469E7B-3115-4715-8561-8088192D8AEE    20140825    clonazepam 2 mg tablet
123             NO KNOWN ALLERGIES  AF469E7B-3115-4715-8561-8088192D8AEE    20150407    duloxetine 60 mg capsule,delayed release
123             NO KNOWN ALLERGIES  AF469E7B-3115-4715-8561-8088192D8AEE    20160222    atorvastatin 40 mg tablet

I'm not sure how to modify the join to get the below result set with NULL

prob_person_id  allergens           med_person_id                           start_date  medication_name
--------------  ---------           -------------                           ----------  ---------------
123             NO KNOWN ALLERGIES  AF469E7B-3115-4715-8561-8088192D8AEE    20140819    Seroquel 50 mg tablet
NULL            NULL                NULL                                    20140819    trazodone 50 mg tablet
NULL            NULL                NULL                                    20140825    clonazepam 2 mg tablet
NULL            NULL                NULL                                    20150407    duloxetine 60 mg capsule,delayed release
NULL            NULL                NULL                                    20160222    atorvastatin 40 mg tablet

(1) This type of manipulation is usually best done in the presentation layer; (2) Tag the question with the database you are using. — Gordon Linoff, Jun 02 '16 at 16:50

score 1 · Answer 1 · answered Jun 09 '16 at 15:22

You can use Rank() to achieve this.

--Setup test data
DECLARE @alergen TABLE (Name VARCHAR(16))
DECLARE @med TABLE (Name VARCHAR(16))

INSERT INTO @alergen
SELECT 'peanuts'

INSERT INTO @med 
SELECT 'acetomenophene'
UNION
SELECT 'ibuprofen'


--Show how rank will assign a row number to each record
SELECT RANK() OVER (ORDER BY name)
    , Name
FROM @alergen

SELECT RANK() OVER (ORDER BY name)
    , Name
FROM @med


--Combine the ranking queries to get the desired result
SELECT *
FROM (
    SELECT RANK() OVER (ORDER BY name) AS 'row_number'
        , Name
    FROM @alergen

) alergens
-- Use FULL JOIN so that all records are included even if there are row_numbers in one list that aren't in the other
FULL JOIN (
    SELECT RANK() OVER (ORDER BY name) AS 'row_number'
        , Name
    FROM @med
) meds ON alergens.row_number = meds.row_number

remove duplicate rows when joining 2 tables

1 Answers1