Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2010
    Posts
    3

    Question Unanswered: Many-To-Many Relation: Getting Missing Data

    I have 3 tables with some fields for a Many-to-Many relationship

    tbl_Member : MemberID, LastName, FirstName
    tbl_Training : TrainingID, TrainingDescription
    tbl_MemberTraining : MemberID, TrainingID, TrainingDate

    I can get all the data, but i would like to get the missing data: Meaning all the Trainings that a Member has no information for in the tbl_MemberTraining.

    Example:
    tbl_Member:
    ID1, Reznor, Trent
    ID2, Keenan, James

    tbl_Training:
    TN1, Fire Safety
    TN2, Ladder Safety

    tbl_MemberTraining:
    ID1, TN1, 1/1/2000
    ID1, TN2, 2/2/2000

    the SQL would show that Keenan is missing data for Fire and Ladder Safety.

    Thank you very much.



    I hope that this makes sense.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Sure does.

    What had you come up with so far? Post your best effort and we'll see what it will take to get you the rest of the way.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Oct 2010
    Posts
    3

    Red face Query so Far....

    I got this far. The query in () is getting every possible combination, the Main query selects the memberID, trainingID that exist.
    -------------------------------------
    SELECT MemberID, TrainingID
    FROM tbl_MemberTraining
    WHERE NOT EXISTS
    (
    SELECT m.MemberID, t.TrainingID
    FROM tbl_Member AS m, tbl_Training AS t
    );
    -------------------------------------
    If if take out the NOT it shows all matches that have data (same as the top query) so I thought to add the NOT and get the opposite, No Luck. If i make it an IN or NOT IN it fails.
    Last edited by Slayer213; 10-08-10 at 10:30.

  4. #4
    Join Date
    Oct 2010
    Posts
    3

    Cool OK I got IT

    After thinking about it, i swapped the queries and boom goes the dynamite.

    SELECT m.MemberID, t.TrainingID
    FROM tbl_Member AS m, tbl_Training AS t
    WHERE NOT EXISTS
    (
    SELECT *
    FROM tbl_MemberTraining
    Where m.MemberID = memberID and t.trainingID = trainingID
    )


  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I don't know if that is genius or luck but I love the Cartesian product you are creating there. I'll go for genius - good job!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the cartesian product should probably be written as an explicit CROSS JOIN

    and i personally shy away from subqueries, especially subqueries with NOT, and especially correlated subqueries
    Code:
    SELECT m.MemberID
         , t.TrainingID
      FROM tbl_Member AS m
    CROSS
      JOIN tbl_Training AS t
    LEFT OUTER
      JOIN tbl_MemberTraining AS mt
        ON mt.MemberID = m.MemberID 
       AND mt.TrainingID = t.TrainingID
     WHERE mt.MemberID IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937 View Post
    i personally shy away from subqueries
    I personally shy away from LOJ checking existence. From both a logical and physical persepective I prefer EXISTS.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Tags for this Thread

Posting Permissions

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