SQL Server – Joining two tables directly or, sometimes, through a third table

I don’t have control over the database setup. I have these:

Things has two fields: ListID, FormID.
Lists also has a FormID.

  • Is where a way to create an SQL Server login with MSBUILD?
  • filter the most recent line, basing on the date
  • SQL Server Agent job dependency
  • Replace function in SQL Server 2014 adds trailing zeros in Char field
  • display multiple images from database to asp.net page
  • Incorrect syntax near the keyword 'DEFAULT' - Unable to modify table
  • So Things can join directly to FormID, or join to FormID through Lists.

    If Thing.ListID is null, I want that Thing.FormID.
    If Thing.ListID is not null, I want Lists.FormID.

    I’m guaranteed to have either Things.FormID be NULL, or Things.ListID be NULL; never both, nor neither.

    Here’s what I had:

    SELECT
        t.ThingID
    FROM
        Questions q JOIN
        Forms f 
            on q.FormID = f.FormID JOIN
        Lists l
            on f.FormID=l.FormID JOIN
        Things t
            on ((t.FormID = f.FormID) OR
                (t.ListID = l.ListID))
    

    Obviously, I have no idea how OR inside JOINs works.

    I got this working using a UNION, but I didn’t know if there was a smarter/faster conditional-triangle-join, something along those lines.


    Not explained here but I want to keep joining off of ThingID. Imagine I’m going to keep joining up tables based on these.

  • SQL group with Recursive CTE
  • Syntax error when using OFFSET, ORDER BY (Bit and Date DESC) and WHERE in my SQL Query
  • How to use multiple DATEPART functions in CASE statement?
  • SQL search all columns of a table for text value
  • What is a 'multi-part identifier' and why can't it be bound?
  • How to generate a range of dates in SQL Server
  • One Solution collect form web for “SQL Server – Joining two tables directly or, sometimes, through a third table”

    You can do what you want with coalesce() and left outer join:

    SELECT coalesce(tf.ThingID, tl.thingID) as ThingID
    FROM Questions q JOIN
         Forms f 
         on q.FormID = f.FormID JOIN
         Lists l
         on f.FormID = l.FormID LEFT JOIN
         Things tf
         on tf.FormID = f.FormID LEFT JOIN
         Things tl
         on tl.ListID = l.ListID;
    

    This produces different results if there is a match both the form and list. However, you have said in the question that this doesn’t happen.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.