Joining the third table directly with the first or through an intermediary

Question

I am trying to join 3 tables where the third table contains a link to the second and the first; and I want to get the result for any of these links. However, when the third table refers to the first, I do not need the result from the second.

It is hard to explain, so I illustrated what I need with the example below.

The first select statement produces 6 lines; I understand why, but that’s not what I want.

The second 2 gives the result that I need, but they have a smell of code. Can anyone advise a better approach to achieve the same result?

See also SQL Fiddle: http://sqlfiddle.com/#!3/8029cc/1

--code to setup my example

    declare @t1 table (id bigint, val nvarchar(10))
    declare @t2 table (id bigint, t1Id bigint, val2 nvarchar(10))
    declare @t3 table (id bigint, t1Id bigint, t2Id bigint, val3 nvarchar(10))

    insert @t1 (id, val) values (1, '1.1')

    insert @t2 (id, t1Id, val2) 
    values (1, 1, '2.1')
    ,(2, 1, '2.2')
    ,(3, 1, '2.3')

    insert @t3 (id, t1Id, t2Id, val3) 
    values (1, 1, null, 'XXX')
    , (1, null, 1, '3.1')
    ,(2, null, 2, '3.2')
    ,(3, null, 3, '3.3')

.

--this produces 6 results; I only want 4

    select *
    from @t1 t1
    left outer join @t2 t2 
        on t2.t1Id = t1.Id
    left outer join @t3 t3
        on t3.t2Id = t2.Id
        or t3.t1Id = t1.id

.

--this works, but means repeating myself; which I'd prefer not to do if possible (could use a cte to make that simpler in the real world scenario, but still not ideal)

    select t1.id, t2.id, t3.id, t1.val, t2.val2, t3.val3
    from @t1 t1
    left outer join @t2 t2 
        on t2.t1Id = t1.Id
    left outer join @t3 t3
        on t3.t2Id = t2.Id

    union all

    select t1.id, null, t3.id, t1.val, null, t3.val3
    from @t1 t1
    left outer join @t3 t3
        on t3.t1Id = t1.id

.

--this works too, but feels very hacky

    select t1.id, t2.id, t3.id, t1.val, t2.val2, t3.val3
    from @t1 t1
    left outer join 
    (
        select id, t1id, val2
        from @t2

        union all

        select null, null, null

    ) t2 
        on coalesce(t2.t1Id,t1.Id) = t1.Id
    left outer join @t3 t3
        on t3.t2Id = t2.Id
        or (t3.t1Id = t1.id and t2.Id is null)

---

Update

, , ; ...

--another option; again slightly hacky

    select id, id2, id3, val, val2, val3
    from
    (
        select t1.id
        , case when t3.t1Id = t1.id then null else t2.id end id2
        , t3.id id3
        , t1.val
        , case when t3.t1Id = t1.id then null else t2.val2 end val2
        , t3.val3
        , row_number() over (partition by t1.id order by case when t3.t1Id = t1.id then null else t2.id end, t3.id) x
        from @t1 t1
        left outer join @t2 t2 
            on t2.t1Id = t1.Id
        left outer join @t3 t3
            on t3.t2Id = t2.Id
            or t3.t1Id = t1.id
    ) t
    where id2 is not null or x = 1

Answer 1

, /.

SELECT *
FROM t3 
LEFT JOIN t2 
  ON t2.id = t3.t2Id
LEFT JOIN t1
  ON t3.t1Id = t1.id
  AND t2.id IS NULL

Answer 2

, , :

SELECT T.ID, 
       CASE WHEN T3.t2Id IS NULL THEN NULL ELSE  T.T2ID END AS T2ID,
       T3.id AS T3ID,
       T.VAL, 
       CASE WHEN T3.t2Id IS NULL THEN NULL ELSE  T.VAL2 END AS VAL2,
       T3.VAL3
FROM (SELECT T1.id, 
             T1.val,
             T2.Id AS T2ID, 
             T2.val2
      FROM @t1 AS T1 JOIN @t2 AS T2 
                  ON T1.id = T2.t1id
      ) AS T JOIN @t3 AS T3 
           ON T.T2ID = T3.ID
ORDER BY  T3.VAL3

test

Answer 3

quite simply - your mistake is your starting table. Start with table 3 and leave the connection to the others, then add the condition. I.e.

    SELECT * FROM  @t3
    LEFT OUTER JOIN @t1
    ON [@t1].id = [@t3].t1id
    LEFT OUTER JOIN @t2
    ON [@t2].id = [@t3].t2id
    WHERE [@t1].id IS NOT NULL
    OR ( [@t2].id IS NOT NULL AND [@t1].id IS NULL)