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)
5 Comments. Leave new
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
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
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
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
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