CODEWITHSUNDEEP
sqlsql-servert-sql
Sora Nguyễn
Sora Nguyễn

Reputation: 33

Find missing records in ONE-TO-MANY relationship query

I have 3 tables:

Class

Id ClassName
guid 1A
guid 2A

Subject

Id SubjectName
guid Math
guid Biography

SubjectOfEachClass

Id ClassId SubjectId TeacherName
guid guid guid James Bond

The way I wanted these tables to work is:

I ran into some problems, I queried the SubjectOfEachClass table and there are only 95 records. The query command I use to find the missing subjects is:

SELECT *
FROM Subject s
JOIN (
    SELECT *
    FROM SubjectOfEachClass
    WHERE ClassId = 'guid'
) AS sc ON s.Id = sc.SubjectId

I replaced the ClassId several times until I found the class that misses some of the subjects.

I reckon this way of querying is not efficient at all. If I have 100 subjects and 100 classes, there will no chance that I will find the missing subjects.

Upvotes: 3

Views: 613

Answers (3)

eshirvana
eshirvana

Reputation: 24603

to find every missing subject in all classes:

select c.id, c.classname , s.id , s.SubjectName
from class c
cross apply Subject s
where not exists (
   select 1 from SubjectOfEachClass sc 
   where sc.classid = c.id and sc.subjectid = s.id
)

Upvotes: 1

Ed Bangga
Ed Bangga

Reputation: 13016

Your primary table should be SubjectOfEachClass, then those foreign tables Subject and Class will join your primary table.

select *
from SubjectOfEachClass sc
inner join Subject s on s.guid=sc.guid
inner join Class c on c.guid=sc.guid
where sc.ClassId = 'guid'

Upvotes: 1

Yuti Friedlander
Yuti Friedlander

Reputation: 52

Try this:

SELECT c.id AS classId,
       count(sc.id) AS countOfSubjects
FROM SubjectOfEachClass AS sc
INNER JOIN Classes AS c ON c.id = sc.classId
GROUP BY c.id
ORDER BY countOfSubjects

The abnormal values will be floated.

Upvotes: 1

Related Questions