February 6, 2015 at 3:23 pm
I have a table of "applicants" with unique applicant id and another table "reviews" with reviews which has unique id and Emplid and contains general program name like Math and then may contain 3 addition rows for specific program like Calculus, algebra, geometry etc.
There may or may not be a record for each applicant id in table reviews or there can be 1 or more than one record in reviews based on level of review( General or Specific).
All the general reviews has “Math” as Program_code but if there are more reviews, they can have Program_code like “Cal” , “Abr”, “Geo”
I want to join the tables so I can get all the records from both the tables where Program_code in reviews table is “Math” only.
That is I want to join the table and get all the records from reviews table where the program_code is “Math” only
How can I do that ?
Thanks,
Blyzzard
February 6, 2015 at 3:40 pm
It would be nice to have DDL, sample data and expected results to test but this should give you an idea.
SELECT *
FROM Applicants a
LEFT
JOIN Reviews r ON a.applicantId = r.applicantId AND r.Program_Code = 'Math'
Here's an explanation on why am I using the additional condition for the join criteria.
February 9, 2015 at 2:49 pm
Thank you for the response.
The only problem is there can or cannot be a record for an applicant in the review table and I want to get all the records from Applicants table even if there is no match in the review table, so if I apply the condition where Programcode='Math', then all the records where there is no review in the review table will not be selected.
February 9, 2015 at 2:56 pm
amar_kaur16 (2/9/2015)
Thank you for the response.The only problem is there can or cannot be a record for an applicant in the review table and I want to get all the records from Applicants table even if there is no match in the review table, so if I apply the condition where Programcode='Math', then all the records where there is no review in the review table will not be selected.
Because the Programcode = 'Math' is in the join condition, records where there is no review in the review table will still be retrieved. That simply ensures that where there are records in the review table, ONLY those with Programcode = 'Math' are retrieved.
February 9, 2015 at 3:01 pm
That's why he is applying that condition in the LEFT JOIN clause.
If there is a matching 'MATH' program, it shows it along with the applicant.
If there is no matching program, or there are only non 'MATH' programs, it will show the applicant along with NULLS for the program information.
Sounds to me like what you want.
If its not, give us an example. Give us a script we can run that creates and loads the two test tables with data, and then show us what you would like the query output for that test data to be.
February 9, 2015 at 5:02 pm
amar_kaur16 (2/9/2015)
Thank you for the response.The only problem is there can or cannot be a record for an applicant in the review table and I want to get all the records from Applicants table even if there is no match in the review table, so if I apply the condition where Programcode='Math', then all the records where there is no review in the review table will not be selected.
I wonder if Nevyn and Mark made it clear to you. That was why I included an article that explains the same problem that you're having and shows you the logic behind the solution.
If you have any questions, be sure to ask them so you can understand it completely
February 10, 2015 at 10:47 am
Thank you all.
I was joining on applicantid earler and using the condition where Program_Code=Math using where clause, hence the output was not same what I wanted.
Blyzzard
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply