If anyone here can point me to the correct answer, I would appreciate it.
I have 2 tables, Curriculum and PeopleEnrolled Curriculum has a unique identifier called ProjectKey (This is the primary key for this table as well) , CurriculumId and CurriculumName
PeopleEnrolled has two columns: PersonUniqueKey and CurriculumKey. (No Unique Identifier)
I am trying to join them so that in the PeopleEnrolled table, I can pull in all the corresponding CurriculumId and CurriculumName fields for a PersonUniquekey. The PersonUniqueKey will appear multiple times in the table depending on how many curriculums the person has enrolled in.
The join will occur between CurriculumKey (in PeopleEnrolled) and ProjectKey (in Curriculum)
The number of records in PeopleEnrolled is 25,000
The number of records in Curriculum is 322
HOWEVER, whenever I try to do a inner/right/right outer or any kind of join on them, I get a count of over 169,000 records. According to my judgement, I should get 25,000 records back with a CurriculumID and CurriculumName for every PersonUniqueKey. Why is this happening. Is it because there is no unique primary key in PeopleEnrolled.
Any advice you can give of how to proceed, I would really appreciate it.
After much thought, I realized that I am trying to join two associated tables. None of them have unique keys which makes it impossible to join them. (At Least thats what I think)
Instead I think that I would need to create another associated table via a stored procedure which would pick out all the people enrolled in a curriculum and then for every curriculum/person tuple, search through the entire list of enrollees and try to find a match, and if it cannot insert a null value in the associated table. Then I can use the associated table to do my reporting.