Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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