SQL Puzzle | Join tables (Only First Row)

In this puzzle you have to join these table but only to the first row from the right table, second table. For more details please check the sample input and expected output.

Sample Input

First Table

Id Dt
7 NULL

Second Table

dt1
2017-12-22 12:07:31.800
2017-12-23 12:07:31.800
2017-12-24 12:07:31.800

Expected Output

Id dt1
7 2017-12-22 12:07:31.800
NULL 2017-12-23 12:07:31.800
NULL 2017-12-24 12:07:31.800

Script

Use below script to create table and insert sample data into it.

 ```-- CREATE TABLE T1 ( Id INT, Dt DATETIME ) GO CREATE TABLE T2 ( dt1 DATETIME ) GO INSERT INTO T1 VALUES (7, NULL) GO INSERT INTO T2 VALUES (GETDATE()-2), (GETDATE()-1), (GETDATE()) GO -- ```

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Solution – 1

 ```-- SELECT k.Id,u.dt1 FROM ( SELECT T1.Id, MIN(T2.dt1) dt1 FROM T1 CROSS JOIN T2 GROUP BY T1.Id )k RIGHT JOIN ( SELECT T1.Id,T2.dt1 FROM T1 CROSS JOIN T2 )u ON k.Id = u.Id AND k.dt1 = u.dt1 -- ```

Output-1

 ```-- /*------------------------ SELECT k.Id,u.dt1 FROM ( SELECT T1.Id, MIN(T2.dt1) dt1 FROM T1 CROSS JOIN T2 GROUP BY T1.Id )k RIGHT JOIN ( SELECT T1.Id,T2.dt1 FROM T1 CROSS JOIN T2 )u ON k.Id = u.Id AND k.dt1 = u.dt1 ------------------------*/ Id dt1 ----------- ----------------------- 7 2017-12-22 12:07:31.800 NULL 2017-12-23 12:07:31.800 NULL 2017-12-24 12:07:31.800 (3 row(s) affected) -- ```

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com