dynamic query for join

  • AntonyO

    Mr or Mrs. 500

    Points: 520

    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)?

    qrydynamic

    Thank you

  • Grant Fritchey

    SSC Guru

    Points: 396551

    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 Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Phil Parkin

    SSC Guru

    Points: 244578

    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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Grant Fritchey

    SSC Guru

    Points: 396551

    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 Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • AntonyO

    Mr or Mrs. 500

    Points: 520

    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')
    go

    I 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

     

  • Grant Fritchey

    SSC Guru

    Points: 396551

    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 Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • AntonyO

    Mr or Mrs. 500

    Points: 520

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

  • Grant Fritchey

    SSC Guru

    Points: 396551

    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 Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • AntonyO

    Mr or Mrs. 500

    Points: 520

    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

     

  • Grant Fritchey

    SSC Guru

    Points: 396551

    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 Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply