SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast justinpalmer's Avatar
    Join Date
    Oct 2005
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Three Table Join Looking for NULL's

    Hi,

    I have the following table structures and data:

    Profile Table:
    student_id, first_name, last_name, program
    1, bob, jones, 51
    2, mary, jane, 52
    3, james, smith, 10

    Evaluation Lookup Table:
    evaluation_id, program, month
    1, 51, 10
    2, 51, 11
    3, 51, 12
    4, 52, 2
    5, 52, 3

    Evaluation Table:
    id, student_id, evaluation_id
    1, 1, 1
    2, 2, 4
    3, 2, 5

    What I am looking for:

    I want to know the total count of all evaluations not completed for program 51. The answer in this case should be 2 (evaluations not completed), because bob jones did not fill out evaluation_id's 2 or 3.

    Any help is greatly appreciated.

    Regards,

    Justin Palmer
    Justin Palmer
    MySQL v.5

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    looks to me like james smith didn't fill out any

    and nobody filled out an evaluation for january
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast justinpalmer's Avatar
    Join Date
    Oct 2005
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    looks to me like james smith didn't fill out any

    and nobody filled out an evaluation for january
    You right.

    James Smith is on program 10, so I don't need to worry about him. I only want to know students that are on program 51 how did not fill out an evaluation for there program.

    There is no January evaluation. This is a month that is taken off by all program participants.

    Regards,

    Justin
    Justin Palmer
    MySQL v.5

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    select p.student_id
         , p.first_name
    	 , p.last_name
      from profile p
      join evaluation_lookup el
        on el.program = p.program
    left outer
      join evaluation e
        on e.evaluation_id = el.evaluation_id
       and e.student_id = p.student_id
     where p.program = 51
       and e.evaluation_id is null
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  5. #5
    SitePoint Enthusiast justinpalmer's Avatar
    Join Date
    Oct 2005
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking

    Hi longneck,

    Thanks that was spot on.

    Regards,
    Justin Palmer
    MySQL v.5


Bookmarks

Posting Permissions

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