SQL SERVER – Relating Unrelated Tables – A Question from Reader

I often get emails from my blog readers asking various types of questions. Here is one of the interesting questions from a reader about Relating Unrelated Tables. Let us go over it in detail.

Users have two tables Table_A and Table_B. I have shown the output of both the tables in the following image. Users want to relate both of the unrelated tables and display output in the format which is displayed right below it. The question is how to relate two unrelated tables and generate the output.

I personally do not understand why there is such a requirement to relate unrelated tables. If you look at the output, the requirement is to get all rows from the first table and have a name from the second table.

Let us generated two tables – Table_A and Table_B.

USE TEMPDB
GO
--
CREATE TABLE TABLE_A
(
A_ID INT
)
GO
INSERT INTO TABLE_A (A_ID)
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5
GO
--
CREATE TABLE TABLE_B
(
B_NAME VARCHAR(100)
)
GO
INSERT INTO TABLE_B (B_NAME)
SELECT 'Mike' UNION ALL
SELECT 'Jim' UNION ALL
SELECT 'Roger'
GO

Let us see how we can generate the requested resultset with the help of row_number() function.

SELECT T1.A_ID,COALESCE(T2.B_NAME,'') AS B_NAME FROM TABLE_A AS T1 LEFT JOIN
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS SNO,B_NAME FROM TABLE_B
) AS T2 ON T1.A_ID=T2.SNO

The idea is to generate serial number “as is” without ordering by any column and join it with the first table. Note that if the first table does not have serial numbers and if there are gaps you need to use the row_number function on both the tables and join them.

I do not see any real-time usage for these type of questions but might be good exercise if you are learning T-SQL.

Reference: Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
Puzzle – Datatime to DateTime2 Conversation in SQL Server 2017
Next Post
SQL Puzzle – Correct the Incorrect Query – Win Price Worth USD 1000 – Aggregate and Subquery

Related Posts

5 Comments. Leave new

  • Michael Tobisch
    March 29, 2019 3:59 pm

    Pinal,

    it does not exactly the same result (as I need an ORDER BY clause for my solution), but what came to my mind when reading the puzzle was using a CTE, so my solution is:

    ;WITH acte AS (
    SELECT a.A_ID,
    ROW_NUMBER() OVER (ORDER BY A_ID ASC) aRow
    FROM dbo.TABLE_A a
    ), bcte AS (
    SELECT b.B_NAME,
    ROW_NUMBER() OVER (ORDER BY B_NAME ASC) bRow
    FROM TABLE_B b
    )
    SELECT
    A_ID,
    B_NAME
    FROM
    bcte bc
    RIGHT JOIN acte ac ON ac.aRow = bc.bRow

    Result
    1 Jim
    2 Mike
    3 Roger
    4 NULL
    5 NULL

    Is there a way to get the same result as you using a CTE?

    Best wishes
    Michael

    Reply
  • Michael Tobisch
    March 29, 2019 4:02 pm

    OK, me again…

    ;WITH acte AS (
    SELECT a.A_ID,
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) aRow
    FROM dbo.TABLE_A a
    ), bcte AS (
    SELECT b.B_NAME,
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) bRow
    FROM TABLE_B b
    )
    SELECT
    A_ID,
    B_NAME
    FROM
    bcte bc
    RIGHT JOIN acte ac ON ac.aRow = bc.bRow

    Reply
  • Vishwa Deepak Dwivedi
    April 18, 2019 9:23 pm

    Hi Mike,
    why should we use cte, If we can find it with simple left outer join

    ———————
    select A_ID,isnull(B_Name,”) as B_Name from TABLE_A A left outer join
    (

    select ROW_NUMBER() over(order by B_name) B_ID,B_Name from TABLE_B
    ) B on A.A_ID =B.B_ID

    Reply
  • select (ROW_NUMBER() Over (order by A_ID)) as rowID,A_ID into #t1 from TABLE_A
    select (ROW_NUMBER() Over (order by B_Name)) as rowID,B_NAME into #t2 from TABLE_B

    select t1.A_ID,IsNull(t2.B_NAME,”) from #t1 as t1
    left outer join #t2 as t2 on t1.rowID=t2.rowID

    Reply
  • select (ROW_NUMBER() Over (order by A_ID)) as rowID,A_ID into #t1 from TABLE_A
    select (ROW_NUMBER() Over (order by B_Name)) as rowID,B_NAME into #t2 from TABLE_B

    select t1.A_ID,IsNull(t2.B_NAME,”) from #t1 as t1
    left outer join #t2 as t2 on t1.rowID=t2.rowID

    Reply

Leave a Reply Cancel reply

Menu
Exit mobile version