How to join the tables to get only record with specific field value in a table

  • 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

  • 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.

    http://www.sqlservercentral.com/articles/T-SQL/93039/

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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.

  • 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.

  • 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.

  • 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

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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