Please help me create sql statement for my tables?

Question

I have 2 tables(1.Employee 2.Family). I want write a sql statement that produce the following output.

Pictures

Url : http://www.enterupload.com/8ryuew2anq0q/QUESTION1.jpg.html

Mirror 1 : http://www.freeimagehosting.net/image.php?c9bea5b6b6.png

Mirror 2 : http://0k.010.img98.com/out.php/i342669_QUESTION1.png

Answer 1

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

Answer 2

Looks like just a left join to me.

SELECT Family.Name, Family.Relative, Employee.Id
FROM Employee
LEFT JOIN Family ON Employee.ID = Family.EmployeeID

If you really want that first column that says id and is new unique number then try this

SELECT ROW_NUMBER() OVER (ORDER BY COALESCE(Family.ID,9999999)  ASC, Employee.ID ASC) AS ID, Family.Name, Family.Relative, Employee.Id as EmployeeID
FROM Employee
LEFT JOIN Family ON Employee.ID = Family.EmployeeID