#1
  1. Plays with fire
    Devshed Novice (500 - 999 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    942
    Rep Power
    97

    Left join for this?


    Hi--

    I've got two tables (questions, answers) and need a list of all the questions and if there are answers, I need them, but not all questions will have answers.

    My tables:

    questions.pkey (primary key)
    questions.question


    answers.pkey (primary key)
    answers.answer
    answers.qkey (foreign key for questions)

    Really thought this would work:

    Code:
    SELECT questions.question, answers.answer FROM questions LEFT JOIN answers ON questions.pkey=answers.qkey
    But it's only returning questions that have related answers.

    Any thoughts?
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by Frank Grimes
    Any thoughts?
    it appears you don't seem to have any questions that don't have answers

    because that query is correct for what you want to do
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    74
    Rep Power
    18
    Perform a LEFT OUTER JOIN to retrieve the questions that dont have answers, as well
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by Aurum84
    Perform a LEFT OUTER JOIN to retrieve the questions that dont have answers, as well
    he did

    LEFT JOIN and LEFT OUTER JOIN are equivalent.... OUTER is an optional keyword
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. Plays with fire
    Devshed Novice (500 - 999 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    942
    Rep Power
    97
    Ha. Thanks!

    Next thing I need to do is get all the questions, with whatever matching answers there are for a matching person.

    Could I add another foreign key to the answers table that matches a name in another table? Like this:

    answers.pkey (primary key)
    answers.answer
    answers.qkey (foreign key)
    answers.nkey (foreign key to name table)

    or is a lookup table a better way to go?
    Last edited by Frank Grimes; March 1st, 2013 at 01:54 PM.
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by Frank Grimes
    or is a lookup table a better way to go?
    no, what you have is fine

    it's unlikely that an answer will be supplied by more than one person, right?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. Plays with fire
    Devshed Novice (500 - 999 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    942
    Rep Power
    97
    I'm told this is a Q&A kind of form. So 10 questions many people will answer, but not everyone will answer all the questions.

    Each person will have only 1 answer for a question, but not answer each question if that makes sense.
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence

IMN logo majestic logo threadwatch logo seochat tools logo