SQL Server:Dynamic Join clause

sql server join example
sql server join syntax
sql server inner join with where clause
sql server join multiple tables
sql server outer join
sql server left outer join
sql server default join
sql server right join

I have 2 tables :

TableA

Id extId

TableB

Id1 ID2 ID3 id4

Now I want to write a join on tableA with tableB. Query should first join on TableA.extid=TableB.Id1. If no record found, join should happen on TableA.extid=tableB.Id2 etc.. What is the best way to achieve this in sql server?

Use multiple left joins:

select coalese(a1.id, a2.id, a3.id, a4.id) as a_id, b.*
from b left join
     a a1
     on b.id1 = a1.extid left join
     a a2
     on b.id2 = a2.extid and a1.extid is null left join
     a a3
     on b.id3 = a3.extid and a2.extid is null left join
     a a4
     on b.id4 = a4.extid and a3.extid is null 
where a1.extid is not null or a2.extid is not null or a3.extid ia not null or a4.extid is not null;

Joins indicate how SQL Server should use data from one table to select the rows A join condition defines the way two tables are related in a query by: A query plan can therefore dynamically switch to a better join strategy  SQL JOIN. A JOIN clause is used to combine rows from two or more tables, based on a related column between them. Notice that the "CustomerID" column in the "Orders" table refers to the "CustomerID" in the "Customers" table. The relationship between the two tables above is the "CustomerID" column.

I understand the task as: join the tables on ID1. If the query brings no result, then join the tables on ID2. Etc. If this is correct then:

You can simply join on all IDs and then keep only the rows joined on the "best" ID with a TOP clause:

select top(1) with ties *
from tablea a
join tableb b on a.extid in (b.id1, b.id2, b.id3, b.id4)
order by
  case when a.extid = b.id1 then 1
       when a.extid = b.id2 then 2
       when a.extid = b.id3 then 3
       when a.extid = b.id4 then 4
  end;

If you rather mean rows (i.e. if there is no match for ID1 for a TableA row in TableB, then try ID2, etc.), then you'd use the same technique, but use a window function partitioned by ID instead:

select top(1) with ties *
from tablea a
join tableb b on a.extid in (b.id1, b.id2, b.id3, b.id4)
order by 
  row_number() over (
    partition by a.id
    order by 
      case when a.extid = b.id1 then 1
           when a.extid = b.id2 then 2
           when a.extid = b.id3 then 3
           when a.extid = b.id4 then 4
      end);

Sample data:

TableA

ID   | EXTID
-----+------
100  | 1
200  | 2
300  | 3
400  | 4

TableB

ID1 | ID2 | ID3 | ID4
----+-----+-----+----
2   | 3   |     |
2   | 4   |     |
3   | 4   |     |
3   | 2   | 4   | 1

Result for query #1 (all matches on ID1):

ID  | EXTID | ID1 | ID2 | ID3 | ID4
----+-------+-----+-----+-----+----
200 | 2     | 2   | 3   |     |
200 | 2     | 2   | 4   |     |
300 | 3     | 3   | 4   |     |
300 | 3     | 3   | 2   | 4   | 1

Result for query #2 (first matching ID):

ID  | EXTID | ID1 | ID2 | ID3 | ID4
----+-------+-----+-----+-----+----
100 | 1     | 3   | 2   | 4   | 1
200 | 2     | 2   | 3   |     |
200 | 2     | 2   | 4   |     |
300 | 3     | 3   | 4   |     |
300 | 3     | 3   | 2   | 4   | 1
400 | 4     | 2   | 4   |     |
400 | 4     | 3   | 4   |     |

Dynamic SQL with Inner Join – Learn more on the SQLServerCentral forums. In my solution, however, I've used the AS syntax, rather that the [name] = [value] syntax. It you're using a Or can it easily kill your server? In SQL Server, a SQL statement is compiled the first time the database sees it. This means that anything that can change the code being generated can't be a parameter. Which in turn means that you can't use parameters for table names; join expressions; column names; where clauses; order by clause

UNION might be a good option if there are known number of Id1, Id2, Id3, Id4, etc.

SELECT ta.Id,
ta.extId,
tb.Id1 AS JoinedID
FROM TableA ta
INNER JOIN TableB tb ON ta.extId = tb.Id1

UNION

SELECT ta.Id,
ta.extId,
tb.Id2 AS JoinedID
FROM TableA ta
INNER JOIN TableB tb ON ta.extId = tb.Id2

UNION

SELECT ta.Id,
ta.extId,
tb.Id3 AS JoinedID
FROM TableA ta
INNER JOIN TableB tb ON ta.extId = tb.Id3

UNION

SELECT ta.Id,
ta.extId,
tb.Id4 AS JoinedID
FROM TableA ta
INNER JOIN TableB tb ON ta.extId = tb.Id4

Dynamically choosing inner or outer join – Learn more on the The query is pretty big and uses old fashioned joins in the WHERE clause (=, =* and *=). Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci) By using joins, you can retrieve data from two or more tables based on logical relationships between the tables. Joins indicate how SQL Server should use data from one table to select the rows in another table. A join condition defines the way two tables are related in a query by: Specifying the column from each table to be used for the join. A

is it possible to create a dynamic query which will output the right thing SQL is a strictly typed language. Which ever way you try, you need two round trips to the database server for your query with dynamic result type:. Dynamic SQL is SQL that is created and executed at run-time. It sounds complicated, but it really isn’t. Instead of having the statements typed directly into the stored procedure, the SQL statements are first built and defined in variables. The code in these variables is then executed.

In this video, we show how to write clean dynamic SQL queries and execute them with Python Duration: 6:56 Posted: May 18, 2015 To learn more about stored procedure development check out this tutorial. Dynamic SQL commands using EXEC. With this approach you are building the SQL statement on the fly and can pretty much do whatever you need to in order to construct the statement.

T-SQL Tuesday is a way for SQL Server bloggers to share ideas about s ON o.​schema_id = s.schema_id INNER JOIN sys.all_columns c ON  In the stored procedure, I want to construct a SQL with a dynamic WHERE clause to fetch ReportIds. The WHERE clause will put a AND operator between all NON NULL fields and compare with the passed parameters. As per the given table data, the non-null field in first row is “Field1”. So the WHERE clause will be.

Comments
  • Does this a do my job for me question? please show us what did you try at very least.
  • Seems like two OUTER JOINs with COALESXE().
  • Is there a reason to have four TableA-IDs in your TableB? On first glance this looks like a bad idea.
  • I understand this as: "select * from a join b on a.id = b.id1. If this brings back an empty result, do select * from a join b on a.id = b.id2. If this is empty again, then ...". Is this correct? Are you talking of the query result like I assume? Or are you talking of the result per row instead? I.e. if for a row in TableA there is no match in TableB for ID1, then try2, etc.
  • @ThorstenKettner you are correct in your first assumption.if first join doesnt return result then 2nd join with different id from table b..Right now I am using this approach.