Sql table join help

Question

Post reply

Sql table join help

SQL Bee

Old Hand

Points: 367
More actions
November 30, 2022 at 10:54 am

#4119972

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.

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

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

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

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.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 2

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.

Change is inevitable... Change for the better is not.

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

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 3

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.

MarkP Old Hand Points: 305 More actions · Answer 4

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;