Har följande:
SELECT c.ChildId, (c.LastName + ', ' + c.FirstName) AS Name, c.Picture, c.Birthday, d.Name AS DepartmentName, isnull(s.Firstname+ ', ' + isnull(s.LastName, ''),'Saknas') AS StaffName, isnull(p1.Firstname+ ', ' + isnull(p1.LastName, ''),'Saknas') AS Parent1Name, isnull(p2.Firstname+ ', ' + isnull(p2.LastName, ''),'Saknas') AS Parent2Name
FROM Child AS c
INNER JOIN Department AS d
ON c.DepartmentId = d.DepartmentId
LEFT OUTER JOIN Staff AS s
ON c.StaffId = s.StaffId
LEFT OUTER JOIN ParentToChild AS ptc1
ON c.ChildId = ptc1.ChildId
LEFT OUTER JOIN Parent AS p1
ON ptc1.ParentId = p1.ParentId
LEFT OUTER JOIN ParentToChild AS ptc2
ON c.ChildId = ptc2.ChildId AND ptc2.ParentId != p1.ParentId
LEFT OUTER JOIN Parent AS p2
ON ptc2.ParentId = p2.ParentId
WHERE c.DepartmentId = @DepartmentId or @departmentId = 0
ORDER BY c.LastName ASC, c.FirstName ASC
Denna fråga returnerar samma Child två gånger om den har två parent. Har provat med distinct, men blir ingen skillnad. Någon som har en lösning??
Tacksam för all hjälp :stud