How to return the right parents

Transact-SQL
1

Hi,

Some one please help me. I think there must be a solution.

I want to return a set of student parent list based on certain criteria.

Here are sample data:

IF OBJECT_ID('tempdb..#TempStudent') IS NOT NULL
DROP TABLE #TempStudent

CREATE TABLE #TempStudent (
[SID] INT NOT NULL
,[Name] NVARCHAR(100) NULL
)

INSERT INTO #TempStudent
SELECT 1,'Jerry' UNION ALL
SELECT 2,'Joe' UNION ALL
SELECT 3,'Jil' UNION ALL
SELECT 4,'Tory'

IF OBJECT_ID('tempdb..#TempParents') IS NOT NULL
DROP TABLE #TempParents

CREATE TABLE #TempParents (
[PID] INT NOT NULL
,[SID] INT NOT NULL
,[Name] NVARCHAR(100) NULL
,[Fin_Resposible] CHAR(1) NULL
,[Live_With] CHAR(1) NULL
,[Relation] NVARCHAR(10) NULL
,[Order_By] Numeric(2,0) NULL
)

INSERT INTO #TempParents
SELECT 1, 1, 'Daddy',Null,'Y','Fa', 1 UNION ALL
SELECT 2, 1, 'Matha',NULL,'', 'Mom', 2 UNION ALL

SELECT 3, 2, 'Casey','Y','Y', 'Fa', NULL UNION ALL
SELECT 4, 2, 'Peter','Y','Y','Other', 1 UNION ALL
SELECT 5, 2, 'Nancy','Y','Y','Mom', 2 UNION ALL

SELECT 6, 3, 'Barbara','Y','','Other', 1 UNION ALL
SELECT 7, 3, 'Mimi','Y','Y','GrandM', 2 UNION ALL
SELECT 8, 3, 'Brien', 'Y','','Fa', NUll UNION ALL

SELECT 9, 4, 'Pam','Y','','Other', 1 UNION ALL
SELECT 10, 4, 'Mel','N','Y','Aunt', 2

Criteria 1 if no parent is fin responsible, returns stu and Null for parent.
This is 1 Jerry's case and next left join worked.
SELECT * FROM #TempStudent s left Join #TempParents p on s.sid = p.sid and p.Fin_Resposible ='Y'

Criteria 2 if multiple parents are fin responsible and lives with, the picking order should be Mom, Fa, GrandM, Other.
In 2 Joe's case, this next seems work. However, it did not bring in Jerry.

SELECT * FROM #TempStudent s left Join #TempParents p on s.sid = p.sid and p.Fin_Resposible ='Y'
WHERE 1 = Case When p.Relation ='Mom' then 1
Else 0
end

But further expended out, it run into trouble.
SELECT * FROM #TempStudent s left Join #TempParents p on s.sid = p.sid and p.Fin_Resposible ='Y'
WHERE 1 = Case When p.Relation ='Mom' then 1
Else Case when p.Relation ='Fa' then 1
Else 0
end
End

Criteria 3 if multiple fin responsible, but live with one, returns the one.
3 Jil's case, should return Mimi

Criteria 4 if only one fin responsible, returns the one.
4 Tory's case, should return Pam.

This next approach got 1 and 4, but missed 2 and 3.
With Parents as
(
Select P.PID, p.Name, stu.sid, p.Relation , ROW_NUMBER() over (partition by stu.SID order by p.Relation ) rn
from #TempStudent stu left Join #TempParents p on stu.sid = p.sid and p.Fin_Resposible ='Y'
and p.Relation in ('Mom','Fa','Other','GrandM')
)
Select S.name as STU, p.name as Par from #TempStudent s left Join Parents p on s.sid = p.sid and p.rn =1

Finally, I have included the "order_by" but did not use it. If I have to "populate" something for the task, what will it be?

Thanks!

2

If I understand correctly, then this:

SELECT s.sid, s.Name AS Student_Name, p.pid, p.name AS Parent_Name, p.Fin_Resposible, p.Live_With, p.Relation
FROM #TempStudent s
LEFT OUTER JOIN (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY sid ORDER BY Fin_Resposible DESC, Live_With DESC, 
        CASE Relation WHEN 'Mom' THEN 10 WHEN 'Fa' THEN 20 WHEN 'GrandM' THEN 30 ELSE 90 END ) AS row_num
    FROM #TempParents
) AS p ON p.sid = s.sid
WHERE p.row_num = 1
--ORDER BY <whatever>
1 Like
3

I see. Clever! Excellent! Thanks!