How do I join getting one row from the left table, no matter how many matches i get from the right table? I have two tables - one is a data table and the other is a mapping table. I want to join them together, but only preserve the data from the left table. However, it is possible that the mapping table may contain multiple records that match to a single record in the data table. I cannot use a DISTINCT because there may be identical rows in the data table, and I want to preserve the same number of rows from that table in the result set.

Here is a sample of the data I am working with:

       DataTable                    MappingTable                 Expected Result
+-----+-----+-----+-----+   +------+------+------+------+   +-----+-----+-----+-----+
| ID1 | ID2 | ID3 | ID1 |   | ID1  | ID2  | ID3  | ID1  |   | ID1 | ID2 | ID3 | ID1 |
+-----+-----+-----+-----+   +------+------+------+------+   +-----+-----+-----+-----+
|  1  |  1  |  1  |  1  |   |  1   | NULL | NULL | NULL |   |  1  |  1  |  1  |  1  |
|  1  |  1  |  1  |  1  |   | NULL | NULL | NULL |  1   |   |  1  |  1  |  1  |  1  |
|  2  |  1  |  1  |  1  |   |  3   |  3   | NULL | NULL |   |  2  |  1  |  1  |  1  |
|  3  |  1  |  1  |  3  |   +------+------+------+------+   |  2  |  2  |  1  |  1  |
|  4  |  1  |  1  |  4  |                                   |  3  |  3  |  1  |  3  |
|  2  |  2  |  1  |  1  |                                   |  2  |  1  |  0  |  1  |
|  3  |  2  |  1  |  3  |                                   |  2  |  1  |  0  |  1  |
|  3  |  3  |  1  |  3  |                                   +-----+-----+-----+-----+
|  2  |  1  |  0  |  1  |
|  2  |  1  |  0  |  1  |
|  4  |  3  |  2  |  3  |
+-----+-----+-----+-----+


Below is the join I am using. I wrote a custom function to handle the NULL-matching behavior, which I am including here as well.

SELECT * FROM DataTable P
JOIN MappingTable M ON dbo.fNullMatchCheckIntS(P.ID1,M.ID1,0,1) = 1
    AND dbo.fNullMatchCheckIntS(P.ID2,M.ID2,0,1) = 1
    AND dbo.fNullMatchCheckIntS(P.ID3,M.ID3,0,1) = 1
    AND dbo.fNullMatchCheckIntS(P.ID4,M.ID4,0,1) = 1




CREATE FUNCTION dbo.fNullMatchCheckIntS (
    @Value1 INT
    ,@Value2 INT
    ,@AutoMatchIfValue1IsNull BIT
    ,@AutoMatchIfValue2IsNull BIT
)
    RETURNS BIT
AS

BEGIN

    DECLARE @Result BIT = 0

    SELECT
        @AutoMatchIfValue1IsNull = ISNULL(@AutoMatchIfValue1IsNull,0)
        ,@AutoMatchIfValue2IsNull = ISNULL(@AutoMatchIfValue2IsNull,0)

    IF
        (@AutoMatchIfValue1IsNull = 1 AND @Value1 IS NULL)
        OR (@AutoMatchIfValue2IsNull = 1 AND @Value2 IS NULL)
        OR @Value1 = @Value2
        OR (@Value1 IS NULL AND @Value2 IS NULL)
    BEGIN
        SET @Result = 1
    END

    RETURN @Result
END


The problem with the way the join works is that the first two rows in the DataTable match on the first two rows in the MappingTable, giving me four identical records in the result, but I only want 2. I know that I could add an identity column to the DataTable and then use DISTINCT or PARTITION to get the result I am looking for, but I would like to avoid that route if possible.

EDIT: I figured out a way to do this using EXISTS, but it looks a little ugly in my opinion. Still interested in other answers if anyone has an idea. Thanks!

SELECT * FROM DataTable D
WHERE EXISTS (
    SELECT D.ID1, D.ID2, D.ID3, D.ID4
    FROM MappingTable M 
    WHERE dbo.fNullMatchCheckIntS(D.ID1,M.ID1,0,1) = 1
        AND dbo.fNullMatchCheckIntS(D.ID2,M.ID2,0,1) = 1
        AND dbo.fNullMatchCheckIntS(D.ID3,M.ID3,0,1) = 1
        AND dbo.fNullMatchCheckIntS(D.ID4,M.ID4,0,1) = 1
)
DBFIDDLE

SELECT 
    d.id1, d.id2, d.id3, d.id4
FROM (SELECT ROW_NUMBER() over (ORDER by d1.id1,d1.id2,d1.id3,d1.id4) as r, 
             d1.* 
      FROM DataTable d1) d
INNER JOIN MappingTable m on 
    (m.id1=d.id1 or m.id1 is null) and 
    (m.id2=d.id2 or m.id2 is null) and
    (m.id3=d.id3 or m.id3 is null) and
    (m.id4=d.id4 or m.id4 is null)
group by d.r,d.id1,d.id2,d.id3,d.id4;


output:

id1         id2         id3         id4
----------- ----------- ----------- -----------
1           1           1           1
1           1           1           1
2           1           0           1
2           1           0           1
2           1           1           1
2           2           1           1
3           3           1           3