Results 1 to 5 of 5

Thread: Join question

  1. #1
    Join Date
    Mar 2009

    Unanswered: Join question

    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,

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    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 | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2009
    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)
    ---------- ---------- ---------------
    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?

  4. #4
    Join Date
    Apr 2002
    Toronto, Canada
    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 | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2009
    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!


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts