none
Combine unrelated column to the TSQL query results RRS feed

  • Question

  • Hello Developers,

    I have two tables with the following details,

    create table #TABLE_A (Name varchar(50), Height  int, City varchar(100))

    insert into #TABLE_A values ('Matt',170,'London')

    create table #TABLE_B (IDColumn int identity (1,1), CandidateColumn decimal(10,3))

    insert into #TABLE_B (CandidateColumn) values (1234567.123)

    select * from #TABLE_A

    select * from #TABLE_B

    As you can see, these tables are not related at all. However, I have a unique requirement to produce the results from these tables so that they look like below:

    Name

    Height

    City

    CandidateColumn

    Matt

    170

    London

    1234567.123

    I have a mental freeze, I just can’t work out how I can combine the results of these two tables in a single query to produce the above results given that these tables don’t have a common column to use in the JOIN.

    Please note that the values from TABLE_B (CandidateColumn) are not meant to be aligned or correspond to the rows from TABLE_A. They are meant to be randomly allocated as long as column "CandidateColumn" is appended to TABLE_A output.

    Many thanks,


    Mpumelelo


    • Edited by Mpumelelo S Tuesday, January 14, 2020 11:37 PM
    Tuesday, January 14, 2020 11:12 PM

Answers

  • Hi Mpumelelo S,

    Please try following script . If I have any incorrect understanding , please let me know.

    create table #TABLE_A (Name varchar(50), Height  int, City varchar(100))
    
    insert into #TABLE_A values ('Matt',170,'London'),('Sott',22,'LA')
    
    create table #TABLE_B (IDColumn int identity (1,1), CandidateColumn decimal(10,3))
    
    insert into #TABLE_B (CandidateColumn) values (1234567.123)
    
    ;with cte1 as(
    select *,row_number()over(order by(select 1)) rn  from #TABLE_A
    )
    select Name, Height, City,CandidateColumn 
    from cte1 a 
    full outer join #TABLE_B b on a.rn=b.IDColumn
    /*
    Name       Height      City      CandidateColumn
    ---------- ----------- ---------------------------------
    Matt       170         London    1234567.123
    Sott       22          LA        NULL
    */
    
    ;with cte1 as(
    select *,row_number()over(order by(select 1)) rn  from #TABLE_A
    )
    select Name, Height, City,CandidateColumn 
    from cte1 a 
    join #TABLE_B b on a.rn=b.IDColumn
    /*
    Name       Height      City      CandidateColumn
    ---------- ----------- ---------------------------------
    Matt       170         London    1234567.123
    */

    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.

    • Marked as answer by Mpumelelo S Wednesday, January 15, 2020 9:51 AM
    Wednesday, January 15, 2020 2:53 AM
  • If you need a random association of rows, then try this query too:

    ;
    with A as
    (
        select *, ROW_NUMBER() over (order by Name) as a
        from #TABLE_A
    ),
    B as 
    (
        select *, ROW_NUMBER() over (order by CHECKSUM(NEWID())) as b
        from #TABLE_B
    )
    select A.Name, A.Height, A.City, B.CandidateColumn
    from A, B
    where A.a = B.b
    order by A.Name


    • Edited by Viorel_MVP Wednesday, January 15, 2020 6:23 AM
    • Marked as answer by Mpumelelo S Wednesday, January 15, 2020 9:52 AM
    Wednesday, January 15, 2020 6:17 AM

All replies

  • You don't need to have a "common column" to join the tables.  You just need a rule to decide which row(s) in #TABLE_A go with which row(s) in #TABLE_B.  For example, if you wanted to match EVERY row in A to EVERY row in B, you could do

    SELECT A.Name, A.Height, A.City, B.CandidateColumn
    FROM #TABLE_A A
    CROSS JOIN #TABLE_B B;

    So for us to help you, you need to tell us how to match up the two tables.

    Tom

    Tuesday, January 14, 2020 11:43 PM
  • Hi Mpumelelo S,

    Please try following script . If I have any incorrect understanding , please let me know.

    create table #TABLE_A (Name varchar(50), Height  int, City varchar(100))
    
    insert into #TABLE_A values ('Matt',170,'London'),('Sott',22,'LA')
    
    create table #TABLE_B (IDColumn int identity (1,1), CandidateColumn decimal(10,3))
    
    insert into #TABLE_B (CandidateColumn) values (1234567.123)
    
    ;with cte1 as(
    select *,row_number()over(order by(select 1)) rn  from #TABLE_A
    )
    select Name, Height, City,CandidateColumn 
    from cte1 a 
    full outer join #TABLE_B b on a.rn=b.IDColumn
    /*
    Name       Height      City      CandidateColumn
    ---------- ----------- ---------------------------------
    Matt       170         London    1234567.123
    Sott       22          LA        NULL
    */
    
    ;with cte1 as(
    select *,row_number()over(order by(select 1)) rn  from #TABLE_A
    )
    select Name, Height, City,CandidateColumn 
    from cte1 a 
    join #TABLE_B b on a.rn=b.IDColumn
    /*
    Name       Height      City      CandidateColumn
    ---------- ----------- ---------------------------------
    Matt       170         London    1234567.123
    */

    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.

    • Marked as answer by Mpumelelo S Wednesday, January 15, 2020 9:51 AM
    Wednesday, January 15, 2020 2:53 AM
  • If you need a random association of rows, then try this query too:

    ;
    with A as
    (
        select *, ROW_NUMBER() over (order by Name) as a
        from #TABLE_A
    ),
    B as 
    (
        select *, ROW_NUMBER() over (order by CHECKSUM(NEWID())) as b
        from #TABLE_B
    )
    select A.Name, A.Height, A.City, B.CandidateColumn
    from A, B
    where A.a = B.b
    order by A.Name


    • Edited by Viorel_MVP Wednesday, January 15, 2020 6:23 AM
    • Marked as answer by Mpumelelo S Wednesday, January 15, 2020 9:52 AM
    Wednesday, January 15, 2020 6:17 AM
  • Thank you for the responses. They work perfectly well, exactly what I’m looking for.

    Kind regards,


    Mpumelelo

    Wednesday, January 15, 2020 9:51 AM