Join tables

[Mary Phelps]

I have 2 tables
First table: StudentSubject with StudentId int, SubjectId int
StudentId SubjectId
1 2
1 3
1 6
2 8
2 6

Second table: StudentMarks with StudentId int, SubjectId int, StudentMarks decimal
StudentId SubjectId StudentMarks
1 8 10
1 3 23
1 6 46
2 8 49
2 6 50

I want to get those values from StudentMarks where studentid in both tables is same however subjectid is not same

For example from above data query should return the first row of data because in studentmarks table studentid 1 doesn't have subjectid 8 in studentsubjecttable.

[Erland Sommarskog]

Post by Mary Phelps
I want to get those values from StudentMarks where studentid in both
tables is same however subjectid is not same
For example from above data query should return the first row of data
because in studentmarks table studentid 1 doesn't have subjectid 8 in
studentsubjecttable.

SELECT SM.StudentID, SM.SubjectId, SM.StudentMarks
FROM dbo.StudentMarks SM
WHERE EXISTS (SELECT *
FROM dbo.Students S
WHERE SM.StudentID = S.StudentID)
AND NOT EXISTS (SELECT *
FROM dbo.Students S
WHERE SM.StudentID = S.StudentID
AND SM.SubjectID = S.SubjectID)

[Shawn Legrand]

Would a different way be to join the tables on StudentID equal with a
WHERE clause of SM and S not equal on SubjectID?

Post by Erland Sommarskog

Post by Mary Phelps
I want to get those values from StudentMarks where studentid in both
tables is same however subjectid is not same
For example from above data query should return the first row of data
because in studentmarks table studentid 1 doesn't have subjectid 8 in
studentsubjecttable.

SELECT SM.StudentID, SM.SubjectId, SM.StudentMarks
FROM dbo.StudentMarks SM
WHERE EXISTS (SELECT *
FROM dbo.Students S
WHERE SM.StudentID = S.StudentID)
AND NOT EXISTS (SELECT *
FROM dbo.Students S
WHERE SM.StudentID = S.StudentID
AND SM.SubjectID = S.SubjectID)

[Erland Sommarskog]

Post by Shawn Legrand

Post by Erland Sommarskog

Post by Mary Phelps
I want to get those values from StudentMarks where studentid in both
tables is same however subjectid is not same
For example from above data query should return the first row of data
because in studentmarks table studentid 1 doesn't have subjectid 8 in
studentsubjecttable.

SELECT SM.StudentID, SM.SubjectId, SM.StudentMarks
FROM dbo.StudentMarks SM
WHERE EXISTS (SELECT *
FROM dbo.Students S
WHERE SM.StudentID = S.StudentID)
AND NOT EXISTS (SELECT *
FROM dbo.Students S
WHERE SM.StudentID = S.StudentID
AND SM.SubjectID = S.SubjectID)

Would a different way be to join the tables on StudentID equal with a
WHERE clause of SM and S not equal on SubjectID?

No. The script below demonstrates this. As for why you get different
results - this is left as an exercise to the reader to understand!

CREATE TABLE StudentSubject
(StudentID int NOT NULL,
SubjectID int NOT NULL,
CONSTRAINT pk_StudentSubject PRIMARY KEY(StudentID, SubjectID)
)
INSERT StudentSubject (StudentID, SubjectID)
VALUES(1,2), (1,3), (1,6), (2,8), (2,6)

CREATE TABLE StudentMarks
(StudentID int NOT NULL,
SubjectID int NOT NULL,
StudentMark int NOT NULL,
CONSTRAINT pk_StudentMarks PRIMARY KEY(StudentID, SubjectID)
)
INSERT StudentMarks (StudentID, SubjectID, StudentMark)
VALUES (1, 8, 10), (1, 3, 23), (1, 6, 46),
(2, 8, 49), (2, 6, 50)

SELECT SM.StudentID, SM.SubjectID, SM.StudentMark
FROM dbo.StudentMarks SM
WHERE EXISTS (SELECT *
FROM dbo.StudentSubject S
WHERE SM.StudentID = S.StudentID)
AND NOT EXISTS (SELECT *
FROM dbo.StudentSubject S
WHERE SM.StudentID = S.StudentID
AND SM.SubjectID = S.SubjectID)

SELECT SM.StudentID, SM.SubjectID, SM.StudentMark
FROM dbo.StudentMarks SM
JOIN dbo.StudentSubject S ON S.StudentID = SM.StudentID
AND S.SubjectID <> SM.SubjectID

go
DROP TABLE StudentSubject
DROP TABLE StudentMarks

[Shawn Legrand]

Silly me!

Your code is creating a set intersection of the diff

and mine would be creating a set union filtering out 4 tuples:

SELECT *
FROM dbo.StudentMarks SM
JOIN dbo.StudentSubject S ON S.StudentID = SM.StudentID

StudentID SubjectID StudentMark StudentID SubjectID
----------- ----------- ----------- ----------- -----------
1 3 23 1 2
1 6 46 1 2
1 8 10 1 2
1 3 23 1 3
1 6 46 1 3
1 8 10 1 3
1 3 23 1 6
1 6 46 1 6
1 8 10 1 6
2 6 50 2 6
2 8 49 2 6
2 6 50 2 8
2 8 49 2 8

(13 row(s) affected)

SELECT SM.StudentID, SM.SubjectID, SM.StudentMark
FROM dbo.StudentMarks SM
JOIN dbo.StudentSubject S ON S.StudentID = SM.StudentID
AND S.SubjectID <> SM.SubjectID

StudentID SubjectID StudentMark
----------- ----------- -----------
1 3 23
1 6 46
1 8 10
1 6 46
1 8 10
1 3 23
1 8 10
2 8 49
2 6 50

(9 row(s) affected)

Really should have paid more attention in Relational Algebra!

Post by Erland Sommarskog

Post by Shawn Legrand

Post by Erland Sommarskog

Post by Mary Phelps
I want to get those values from StudentMarks where studentid in both
tables is same however subjectid is not same
For example from above data query should return the first row of data
because in studentmarks table studentid 1 doesn't have subjectid 8 in
studentsubjecttable.

SELECT SM.StudentID, SM.SubjectId, SM.StudentMarks
FROM dbo.StudentMarks SM
WHERE EXISTS (SELECT *
FROM dbo.Students S
WHERE SM.StudentID = S.StudentID)
AND NOT EXISTS (SELECT *
FROM dbo.Students S
WHERE SM.StudentID = S.StudentID
AND SM.SubjectID = S.SubjectID)

Would a different way be to join the tables on StudentID equal with a
WHERE clause of SM and S not equal on SubjectID?

No. The script below demonstrates this. As for why you get different
results - this is left as an exercise to the reader to understand!
CREATE TABLE StudentSubject
(StudentID int NOT NULL,
SubjectID int NOT NULL,
CONSTRAINT pk_StudentSubject PRIMARY KEY(StudentID, SubjectID)
)
INSERT StudentSubject (StudentID, SubjectID)
VALUES(1,2), (1,3), (1,6), (2,8), (2,6)
CREATE TABLE StudentMarks
(StudentID int NOT NULL,
SubjectID int NOT NULL,
StudentMark int NOT NULL,
CONSTRAINT pk_StudentMarks PRIMARY KEY(StudentID, SubjectID)
)
INSERT StudentMarks (StudentID, SubjectID, StudentMark)
VALUES (1, 8, 10), (1, 3, 23), (1, 6, 46),
(2, 8, 49), (2, 6, 50)
SELECT SM.StudentID, SM.SubjectID, SM.StudentMark
FROM dbo.StudentMarks SM
WHERE EXISTS (SELECT *
FROM dbo.StudentSubject S
WHERE SM.StudentID = S.StudentID)
AND NOT EXISTS (SELECT *
FROM dbo.StudentSubject S
WHERE SM.StudentID = S.StudentID
AND SM.SubjectID = S.SubjectID)
SELECT SM.StudentID, SM.SubjectID, SM.StudentMark
FROM dbo.StudentMarks SM
JOIN dbo.StudentSubject S ON S.StudentID = SM.StudentID
AND S.SubjectID <> SM.SubjectID
go
DROP TABLE StudentSubject
DROP TABLE StudentMarks

[Erland Sommarskog]

Post by Shawn Legrand
Really should have paid more attention in Relational Algebra!

You are not the first person to make that lapse. I've seen it before.

[jsfromynr]

Post by Erland Sommarskog

Post by Shawn Legrand
Really should have paid more attention in Relational Algebra!

You are not the first person to make that lapse. I've seen it before.
--

Hi there,

I am not sure why we are using two "Exists " in query, IMHO single would have done same.

Select * from StudentMarks
Where Not Exists(
Select 1 from StudentSubject Where StudentMarks.StudentID = StudentSubject.StudentID
And StudentMarks.SubjectID = StudentSubject.SubjectID
)

Hope it helps
Jatinder Singh
jatindersingh.blogspot.com

[Erland Sommarskog]

Post by jsfromynr
I am not sure why we are using two "Exists " in query, IMHO single would have done same.
I want to get those values from StudentMarks where studentid in both
tables is same however subjectid is not same

So I added the first EXISTS in case there is a student in StudentMarks
which is not in StudentSubjects. (Admittedly that would be somewhat funny,
but I had no more information about the tables that were given in the
original post.)