Having trouble creating a specific view.
-
I'm trying to create a view which combines 2 database and displays data like the following:
HH_ID, STUDENT_ID, PARENT_ID
1000, 6023, 1234
1001, 9246, 4568
1002, 7248, 6548
1003, 6684, 7459
My databases look like this:
StudentDatabase
HH_ID, STUDENT_ID, GRADE
1000, 6023, 12
1000, 7652, 11
1001, 9246, 09
1002, 7248, 11
1003, 9243, 09
1003, 6684, 12
ParentDatabase
HH_ID, PARENT_ID
1000, 1234
1000, 1480
1001, 4568
1001, 9548
1002, 6548
1002, 9482
1003, 7459
I'm trying to figure out how to get the resulting view to combine the databases by HH_ID, but only display the STUDENT_ID of the oldest student, which is the higher gradelevel. As for the PARENT_ID it doesn't matter which appears. Is this even possible? If so, how would the code look?
Thanks in advance.
-
Different databases, or different tables? Looks like the latter, assuming that it is.
This isn't tested, but it should be mostly right.
SELECT HH_ID, StudentID, MAX(Parent_ID) As ParentID
FROM ParentDatabase P INNER JOIN
(SELECT HH_ID, Student_ID FROM StudentDatabase Stu INNER JOIN (SELECT HH_ID, MAX(GRADE) AS MaxGrade FROM StudentDatabase GROUP BY HH_ID) HighestGrade ON Stu.HH_ID = HighestGrade.HH_ID AND Stu.Grade = HighestGrade.MaxGrade
) S ON P.HH_ID = S.HH_ID
GROUP BY P.HH_ID, Student_ID
Since you said it doesn't matter which parentID, I've used Max.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Gilamonster - this will work in most cases. There is however a problem where the there may be more than one record per HH_ID and Grade combination on the join. I don't pretend to understand the model of the database, but I think there would be a problem is there is a student in more than one grade at the same time. This is very unusually - but in my part of the world I have seen students progress to the next year and still repeat part of the previous year, if only to take the exam.
I have seen this type of joining before where you identify a row by is max value and some other columns. There tends to be some scenarios where you can get more than one row and so end up with a broken query.
A.
-
BWT try this.
SELECT
P
.HH_ID,
(SELECT TOP 1 S.Student_ID
FROM StudentDatabase S
WHERE S.HH_ID = P.HH_ID
ORDER BY S.HH_ID,S.GRADE DESC) AS S_Student_ID,
P
.M_PARENT_ID
FROM
(SELECT HH_ID, MAX(Parent_ID) AS M_PARENT_ID
FROM ParentDatabase
GROUP BY HH_ID) AS P
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply