February 23, 2011 at 8:33 am
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
February 23, 2011 at 9:25 am
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'
February 23, 2011 at 9:41 am
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