SQL Server:Dynamic Join clause
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 :
Id1 ID2 ID3 id4
Now I want to write a join on tableA with tableB.
Query should first join on
If no record found, join should happen on
What is the best way to achieve this in sql server?
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
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);
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.
- 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.