dynamic query for join
-
Hi everybody,
I have a table and for every object I need to find further information from other tables (depend on the category).
I don't know how to write a dynamic query to find the information of the costumer (the same name of the field for both table dba and dbb). It could be a way to put in the same query the description too (even the field name_x is changing)?
Thank you
-
I could try to guess at what you're going for, but instead, could you put the table names in with the tables. Show some of the relationships, primary & foreign keys, and the expected output. What would your results look like. Then it may be possible to show a query. Otherwise, I'd just have to guess too much here.
FieldJoin table looks like it links to two different tables? That's a horrible design. Don't do it. Any one column serves a single purpose. If you need to join multiple values to multiple values, the better approach is what's called an interim table. Many of A can join to many of B through a table that has a foreign key to A and a foreign key to B with a pk of the two associated columns.
But please clarify a little and maybe one of us could help.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning - Grant Fritchey wrote:
If you need to join multiple values to multiple values, the better approach is what's called an interim table. .
I don't think 'interim table' is the correct term here ... maybe you meant intermediary table, or intersection table?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
- Phil Parkin wrote:Grant Fritchey wrote:
If you need to join multiple values to multiple values, the better approach is what's called an interim table. .
I don't think 'interim table' is the correct term here ... maybe you meant intermediary table, or intersection table?
I think I've called it an interim table for a couple of decades now. I'll try to change. No promises.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
CREATE TABLE #An_Obj
(id_obj nvarchar(5),
id_category int)
CREATE TABLE #JoinTable
(
id_category int,
dbjoin nvarchar(50),
schemajoin nvarchar(50),
tablejoin nvarchar(50),
fieldjoin nvarchar(50)
)
insert into #An_Obj values ('a1',1)
insert into #An_Obj values ('b2',2)
insert into #JoinTable values (1,'mydb','dbo','db_a','id_a')
insert into #JoinTable values (1,'mydb','dbo','db_b','id_b')
--Create database [mydb]
--go
use mydb
go
create table db_a
(id_a nvarchar(5),
name_a nvarchar(50),
customer nvarchar(50))
go
insert into dba values('a1','description_a1','cust_a1')
go
create table db_b
(id_b nvarchar(5),
name_b nvarchar(50),
customer nvarchar(50))
go
insert into dbb values('b2','description_b2','customerdesc')
goI have all the informations in different tables of the db 'mydb'.
I want to obtain a result like this (using a dynamic query):
I wonder if it's possible to obtain the name too (the suffix of every table's name is the same with the field' name ):
Thanks a lot
-
As you've defined it, a simple UNION query will get you what you want.
SELECT a.id_a as Id_Obj,
a.customer_a as Customer,
a.Name_a as Name
FROM dbo.db_a as a
UNION
SELECT b.id_b as Id_Obj,
b.customer_b as Customer,
b.Name_b as Name
FROM dbo.db_b as b;Is that what you mean?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
no, because the primary key is id_obj , category
I could have the same id_obj with 2 different categories. So, for the category 1 I have to do join with a table, for the category 2 with the other table (reading from #JoinTable, where it is indicated which table a have to use for join for every category ).
-
I'm still not understanding what you're going for. Apologies.
However, I think what you want is two different LEFT JOINs to the two tables, then use COALESCE to get the column that is not null, reference each of your columns from the different tables.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
you're right, it's not very clear...it could be a dynamic query that does a join only for a value of id_obj.
I don't know how to write a dynamic query ,for example if I filter id_obj='a1' and id_category=1
In that case I have to do this join:
select id_obj, id_category, from #An_Obj o inner join [mydb].[dbo],[db_a] d on o.id_obj =d.id_a
but [mydb].[dbo],[db_a] and [id_a] have to be a dynamic part because depends on id_Category (I have to read the value from the table #JoinTable - after I did the join between #An_Obj and #JoinTable for id_category)
if I filter id_obj='b2' and id_category=2 the script will be:
select id_obj, id_category, from #An_Obj o inner join [mydb].[dbo],[db_b] d on o.id_obj =d.id_b
-
I'm just still not getting it.
So, let's try this. You can't have a dynamic join. You have to define a join. Period. To dynamically change whether or not you're joining or what you're joining on, you need to do ad hoc t-SQL. You'll need to build the command and execute it through sp_executesql. Just make sure that you properly parameterize the command. Otherwise, it's a very common vector for injection attacks.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply