I have three tables with columns (bad fake names):
STUDENT (student_id)
STUDENT_UNIQUE_INFO (student_unique_info_id, student_id)
STUDENT_OTHER_INFO (student_other_info_id, student_id)
I wish I could change the schema but I can't. Some students don't have unique_info and/or other_info.
My result set would be like:
STUDENT_ID | STUDENT_UNIQUE_INFO_FIELDS... | STUDENT_OTHER_INFO_FIELDS...
Where the fields could sometimes be null if the student didn't have any.
Problem is most examples would have STUDENT containing the IDs of the other tables, I don't have that option.
I tried something like (outputting to a new obj that takes in all 3 of the tables used to join this thing)
SELECT NEW path.to.outputObj(S,
(SELECT SUI
FROM path.to.SUI SUI
WHERE S.studentId = SUI.studentId),
(SELECT SOI
FROM path.to.SOI SOI
WHERE S.studentId = SOI.studentId))
FROM STUDENT S
But it blew up because some students have multiple entries in student_other_info.
java.sql.SQLException: Subquery returns more than 1 row
I'm just a bit lost at how to even go about this.
You should map those other two tables as lists that could contain 0..n rows for each student.
class Student {
@OneToMany(mappedBy = "student");
List<StudentUniqueInfo> studentUniqueInfoList;
@OneToMany(mappedBy = "student");
List<StudentOtherInfo> studentOtherInfoList;
...
}
And then StudentUniqueInfo.java and StudentOtherInfo.java would each have a field
@JoinColumn(name = "STUDENT_ID")
private Student student;