March 5, 2015 at 5:06 am
Hi,
I am trying to create a table with the following:
Student (one row)
Enrolment (multiple rows) - in this case 2
Work Based Learning (multiple rows) - in this case 4
If I link Student to Enrolment I get 2 rows as expected.
If I link Student to WBL I get 4 rows as expected.
When I link all three I get 8 rows because I link WBL to Student and because of the 2 Enrolments it duplicates the WBL data.
Is a nested query required to solve this?
Can anyone help me please?
Thanks
Simon
Here's my basic code:
SELECT
b.StudentID,
b.Forenames,
b.Surname,
a.TrainingProvider,
a.StartDate,
a.EndDate,
a.PlannedHours,
a.ActualHours,
a.IncludeInStudyProgramme,
b.PMStudentID,
c.CourseID,
d.CourseCode,
d.CourseTitle
FROM LearnerInformation_WBL A
INNER JOIN Student B
ON a.PMStudentID = b.PMStudentID
INNER JOIN Enrolment C
ON b.PMStudentID = c.PMStudentID
INNER JOIN CourseD
ON c.CourseID = d.CourseID
WHERE b.StudentID = 20069141
ORDER BY StudentID,
TrainingProvider
March 5, 2015 at 6:28 am
We'll need sample data for this one so we can tell which key (join predicates) are duplicating.
This link http://www.sqlservercentral.com/articles/Best+Practices/61537/ will show the desired sample data formatting.
edit: obviously don't post confidential data, just give us a sample of what tables include which duplicating key values.
March 5, 2015 at 6:38 am
Thanks. Sorry I should have uploaded code better.
Here is the table:
StudentIDForenamesSurnameTrainingProviderStartDateEndDatePlannedHoursActualHoursIncludeInStudyProgrammePMStudentIDCourseIDCourseCode
12345678FirstnameSurnameAP109/02/2015 00:0013/02/2015 00:00NULL11159139XDMEDIA
12345678FirstnameSurnameAP109/02/2015 00:0013/02/2015 00:00NULL111591102TUT
12345678FirstnameSurnameS3P16/02/2015 00:0020/02/2015 00:0035NULL159139XDMEDIA
12345678FirstnameSurnameS3P16/02/2015 00:0020/02/2015 00:0035NULL1591102TUT
12345678FirstnameSurnameTCCSL16/02/2015 00:0020/02/2015 00:003535159139XDMEDIA
12345678FirstnameSurnameTCCSL16/02/2015 00:0020/02/2015 00:0035351591102TUT
12345678FirstnameSurnameWS12/11/2014 00:0015/11/2014 00:00NULL37159139XDMEDIA
12345678FirstnameSurnameWS12/11/2014 00:0015/11/2014 00:00NULL371591102TUT
March 5, 2015 at 7:07 am
Interestingly enough, your data post looks almost identical to the section of the linked article from my previous post titled "The Wrong Way to Post Data ".
Also you're labelling the data as the contents of "the table", when clearly your first post includes a query that references three tables.
(I know, picky picky, but you don't want to tax the experts with reverse engineering your situation, because chances are they'll just go to the next post that might be following established posting guidelines as discussed in the link I included in my previous post.)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply