none
Combining two sets in query RRS feed

  • Question

  • Hello every one

    i have 2 tables like bellow:

    t1

    1     10

    2     15

    3     14

    ******

    t2

    a    11

    b    9

    c    16

    d   20

    i want to find near objects of t1 with t2 which only gives me one value per record for example if we find a is good for 1

    it should not consider b for 1 again.

    thanks a lot.

    Monday, May 20, 2019 3:42 AM

All replies

  • One way would be to generate a unique row number for each row in both tables, then join on it.

    CREATE TABLE t1(id int, value int);
    INSERT INTO t1 VALUES
    (1,     10),
    (2,     15),
    (3,     14);
    CREATE TABLE t2(id char(1), value int);
    INSERT INTO t2 VALUES
    ('a',    11),
    ('b',    9),
    ('c',    16),
    ('d',   20);
    GO
    
    SELECT *
    FROM
    	(SELECT *, ROW_NUMBER() OVER(ORDER BY id) rn
    	FROM t1) first
    FULL OUTER JOIN
    	(SELECT *, ROW_NUMBER() OVER(ORDER BY id) rn
    	FROM t2) sec
     ON first.rn = sec.rn

    Tuesday, May 21, 2019 3:29 AM
  • Hi MJSQLDev,

     

    Please try following script to see if it satisfies your requirement. If I have any incorrect understanding , please share us your expected result so that we will have a right direction.

     
    IF OBJECT_ID('t1') IS NOT NULL drop table t1
    IF OBJECT_ID('t2') IS NOT NULL drop table t2
    go 
    create table t1
    (ID int,
    Value int)
    insert into t1 values (1,10),(2,15),(3,14)
    create table t2
    (Type varchar(10),
    Value int)
    insert into t2 values ('a',11),('b',9),('c',16),('d',20)
    declare  @table table(ID int,t1_Value int,Type varchar(10),t2_Value int)
    declare @i int =(select count(*) from t1)
    declare @j int =1
    while (@j<=@i)
    begin 
    ;with cte as (
    select ID,t1_Value,Type,t2_Value,row_number()over(order by diff,rn) rn 
    from 
    (select ID,a.Value t1_Value,ABS(a.Value-b.Value) as diff,Type,b.Value t2_Value ,b.rn
    from t1 a cross join (select *,row_number()over(order by Type) as rn  from t2 ) b where ID=@j and not exists (select 1 from @table where b.Type=type) 
    )t)
    insert into @table 
    select ID,t1_Value,Type,t2_Value from cte where rn=1
    set @j=@j+1
    end 
    select * from @table 
    /*
    ID          t1_Value    Type       t2_Value
    ----------- ----------- ---------- -----------
    1           10          a          11
    2           15          c          16
    3           14          b          9
    */
    

     

    Hope it will help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, May 21, 2019 7:36 AM
  • Thank you so much Rachel. it worked very well!

    The problem i have now is performance. i have approximately 250 records per entity so it runs query 250 times.

    Best regards

    Tuesday, May 21, 2019 10:22 AM
  • https://www.red-gate.com/simple-talk/sql/t-sql-programming/celkos-sql-stumper-the-class-scheduling-problem/

    Fields are not  columns.  For 30+ years, SQL forums have required DDL in Postings. Based on your narrative, I will guess you want a T-Join. The above link will help you. But before you post again, please learn SQL  and Netiquette.


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, May 21, 2019 4:03 PM
  • Hi MJSQLDev,

    Thank you for your reply.

     

    A temporary table generally provides better performance than a table variable. The only time this is not the case is when doing an INSERT and a few types of DELETE conditions. This increase in performance is especially evident when dealing with larger data sets as the ability to create indexes on the temporary table speeds up query execution significantly. When the data set is small the difference in performance is almost negligible. In my opinion if you are doing a lot of data manipulation with the temporary object after it has been created and the data set is fairly large a temporary table is the best option.

     

    So you can try to change able variable into temporary table in above script.

     

    Hope it will help you.

     

    Best Regards

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 22, 2019 8:21 AM
  • Thanks a gain Rachel

    I tried to use temps table and of course more parametrized query and eventually met a good performance.

    Tuesday, May 28, 2019 5:31 AM
  • Hi MJSQLDev,

     

    Thank you for your reply.

     

    We are glad to hear that  you have solved your issue ,please kindly mark the helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, May 28, 2019 6:40 AM