Hello,
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.

Thanks very much,
Rakesh.

could you please show a couple of rows of sample data from each table, as well as the query that you tried

should be straightforward to fix

from PeopleEnrolled:
PersonUniqueKey CurriculumKey
--------------- ----------
92308290002038 92308290007635
92308290002038 92308290005512
92308290002038 92308290008353
92308290002038 92308290008354
92308290002038 92308290009353
92308290002038 92308290008264
92308290002038 92308290008622
92308290002038 92308290008272
92308290002038 92308290008289
92308290002038 92308290009723

from Curriculum (only 3 tables listed here, there are more)
XCODE XREQUIRED PROJECTKEY
---------- ---------- ---------------
REC0013 0 92308290009723
SA3084 0 92308290008289
SA3086 0 92308290008289
SA3085 0 92308290008289
REC0013 0 92308290008289
SA3089 1 92308290008622
SA0410 1 92308290008622
2045 1 92308290008622
REC0113 1 92308290008622
REC0114 1 92308290008622
SA1754 1 92308290008622


These are the samples, does this work?

that's very nice, except for several things

first, the column names don't match

second, you said "Curriculum has a unique identifier called ProjectKey (This is the primary key for this table as well)"

however, this does not bear out in the data, as i can see two rows with the same value for ProjectKey, so it ~can't~ be the primary key

perhaps you could show the actual results that you want the query to return, based on the data that you showed

r937, Thank you very much for your help!

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.

Thanks again!

Rakesh.