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.
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.
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
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.