[Solved] Please help me create sql statement for my tables? [closed]

by


This gets your desired results. Not sure why you would desire them though! (particularly the completely made up id values 9 and 10)

;WITH Employee AS
(
select 1 as ID, 'Alex' AS Name UNION ALL
select 2 as ID, 'James' AS Name UNION ALL
select 3 as ID, 'John' AS Name UNION ALL
select 4 as ID, 'Anthony' AS Name UNION ALL
select 5 as ID, 'Jack' AS Name
),
Family AS
(
SELECT 1 AS ID, 'Kayla' AS Name, 'wife' AS Relative, 1 AS EmployeeID UNION ALL
SELECT 2, 'Benjamin', 'Son',1 UNION ALL
SELECT 3, 'Alyssa', 'wife',2 UNION ALL
SELECT 4, 'Emma', 'daughter',2 UNION ALL
SELECT 5, 'Emily', 'daughter',2 UNION ALL
SELECT 6, 'Elizabeth', 'wife',5 UNION ALL
SELECT 7, 'Jackson', 'Son',5 UNION ALL
SELECT 8, 'Laura', 'daughter',5 
),
cte AS
(

SELECT f.ID, f.Name, f.Relative, e.ID EmployeeID,
MAX(f.ID) OVER() + ROW_NUMBER() OVER (PARTITION BY f.ID ORDER BY e.ID) AS RN
FROM Employee e LEFT OUTER JOIN Family f
ON e.ID = f.EmployeeId
)
SELECT COALESCE(ID,RN) AS ID, Name, Relative, EmployeeID 
FROM cte
ORDER BY ID

solved Please help me create sql statement for my tables? [closed]