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
    AS (SELECT CAST(101 AS INT) AS StudID,
    CAST('English' AS VARCHAR(20)) AS Subj,
    CAST(91 AS INT) AS Marks
    UNION
    SELECT 101,
    'Maths',
    76
    UNION
    SELECT 101,
    'Science',
    89),
    tblStudentMarksCopy1
    AS (SELECT CAST(101 AS INT) AS StudID,
    CAST(91 AS INT) AS English,
    CAST(76 AS INT) AS Maths,
    CAST(89 AS INT) AS Science),
    OriginalTwist
    AS (SELECT StudID,
    MAX(English) English,
    MAX(Maths) Maths,
    MAX(Science) Science
    FROM
    (
    SELECT StudID,
    CASE
    WHEN Subj = 'English' THEN
    Marks
    END English,
    CASE
    WHEN Subj = 'Maths' THEN
    Marks
    END Maths,
    CASE
    WHEN Subj = 'Science' THEN
    Marks
    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