Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    6

    Unanswered: How Would I Do this Join?

    Hi, I have 2 tables, JobApplicants and OpenPositions



    JobApplicants looks something like this:

    Name, fkFirstChoice, fkSecondChoice
    Joe, 1, 2
    Henry, 2, 4



    OpenPositions looks something like

    pkPositionID, PositionTitle

    1, Engineer
    2, Programmer
    3, ProjectManager
    4, Accountant



    How would I write a query that would give me output like this?

    Name FirstChoice SecondChoice
    Joe, Engineer, Programmer
    Henry, Programmer, Accountant



    Thanks!

    Aaron
    Last edited by Aaron Edwards; 01-25-05 at 00:39.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Use alias names for your OpenPositions table so that you can link it twice. Use Left Outer joins to be sure that records are returned even if one of the foreign keys is missing:

    Code:
    select	JobApplicants.Name,
    	FirstChoice.PositionTitle,
    	SecondChoice.PositionTitle
    from	JobApplicants
    	left outer join OpenPositions FirstChoice on JobApplicants.fkFirstChoice = FirstChoice.pkPositionID
    	left outer join OpenPositions SecondChoice on JobApplicants.fkSecondChoice = SecondChoice.pkPositionID
    But a bigger question is why anybody would list Programmer as a second choice. Obviously you have some severe data corruption. You should just delete those records.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Sep 2003
    Posts
    6
    True enough. Thanks for your help.

    Aaron

Posting Permissions

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