Sql table join help

  • I have 2 tables .

    tblStudentMarksOriginal -

    StudID, Subject, Marks

    101, english, 91

    101, maths, 76

    101, science, 89

    tblStudentMarksCopy1 -

    studID, English, maths, science

    101, 91, 76, 89

    I need to write sql query to join both tables to check if marks match or not for each student subjectwise. If match then display matching else display nomatch. Results can be displayed in any format- column wise or row wise.

  • We will need your help in order to be able to help you, so please help us!


    It would be greatly appreciated if you could provide the DDL (create table) script, sample data as an insert statement, the desired output from the sample data, and what you have tried so far.

  • To Eirikur's point, what will a row look like in the tblStudentMarksCopy1 table if a student hasn't taken one of the subjects?  Will there always be only 3 subjects in the tblStudentMarksCopy1 table?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Just to expand a bit on previous comments -

    Can a StudentID exist in just 1 of the tables?

    Your sample data Eirikur is asking for should include a few of the different types of mismatches to be helpful. Different scores, StudentID existing in each table without a match in the other, along with a StudentID missing a subject in either / both tables.

    Simple can become a bit more complex as you dig deeper. I always found it helpful to try and think of some of the possible problems first. Data in two places always seems to differ over time. Sample data reflecting this is much better for developing a solution. Or may save you from some rework later on.



  • Example approach based on what you've asked for.

    If you want something else, as the others have said, ask a better question next time.

    WITH tblStudentMarksOriginal
    CAST('English' AS VARCHAR(20)) AS Subj,
    CAST(91 AS INT) AS Marks
    SELECT 101,
    SELECT 101,
    CAST(91 AS INT) AS English,
    CAST(76 AS INT) AS Maths,
    CAST(89 AS INT) AS Science),
    AS (SELECT StudID,
    MAX(English) English,
    MAX(Maths) Maths,
    MAX(Science) Science
    SELECT StudID,
    WHEN Subj = 'English' THEN
    END English,
    WHEN Subj = 'Maths' THEN
    END Maths,
    WHEN Subj = 'Science' THEN
    END Science
    FROM tblStudentMarksOriginal
    ) x
    GROUP BY StudID)
    SELECT t1.StudID, CASE WHEN t2.English = t1.English
    AND t2.Maths = t1.Maths
    AND t2.Science = t1.Science THEN 'Match' ELSE 'No Match' END AS IsMatch
    FROM tblStudentMarksCopy1 t1
    LEFT JOIN OriginalTwist t2
    ON t2.StudID = t1.StudID;


Viewing 5 posts - 1 through 4 (of 4 total)

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