Problem With results from Multiple tables

  • HI, im new in sql server. I have 3 tables : Applicant (ID,Application_Number,Last_Name,First_Name,Other_Names)

    second table ProgramChoice (ID,Number,Applicant_FK,Pos_FK)

    Third table Pos (ID,Pos_Name)

    Each Applicant has one or two entries in ProgramChoice table. The ProgramChoice links Pos by Pos_FK and maps to Applicant by Applicant_FK

    ProgramChoice.Number is 1 for 1st entry,2 for d second entry

    This is the query i use

    SELECT Application_Number,Last_Name,First_Name,Other_Names,

    'First Choice Pos'=CASE Number When 1 Then Pos_Name ELSE '' END

    ,'Second Choice Pos'=CASE Number When 2 Then Pos_Name ELSE '' END

    FROM Applicant INNER JOIN ProgramChoice ON ProgramChoice.Applicant_FK=Applicant.ID

    INNER JOIN Pos ON Pos.Pos_FK=Pos.ID

    WHERE Application_Number='153DC873AD'

    Can some one help me on how to display the results on a single row for each Applicant with the choices.

    Thanks for your contribution in advance

  • Look up doing subselects. You need to select out to get it all on "one row". Otherwise you will want to do the logic in the UI.

    But it would be something similar to as follows (not sure if syntax is correct as I cant test query).

    SELECT A.Application_Number,

    A.Last_Name,

    A.First_Name,

    A.Other_Names,

    (SELECT P.Pos_Name FROM POS P INNER JOIN ProgramChoice PC ON (P.ID = PC.Pos_FK) WHERE PC.Applicant_FK = A.ID AND PC.Number = 1) "FirstChoice"

    (SELECT P.Pos_Name FROM POS P INNER JOIN ProgramChoice PC ON (P.ID = PC.Pos_FK) WHERE PC.Applicant_FK = A.ID AND PC.Number = 2) "SecondChoice"

    FROM Applicant A

    WHERE Application_Number='153DC873AD'

  • Thanks so much. it works but im yet to determine its impact since the data required is huge

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply