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

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. Fo开发者_StackOverflow社区r help clarifying this question so that it can be reopened, visit the help center. Closed 12 years ago.

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

---

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

---

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