Find missing records in ONE-TO-MANY relationship query

user_11315091 0
Viewed 165

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:

  • There will be 10 classes in table Class.
  • There will be 10 subjects in table Subject.
  • Each class will has 10 subjects and for 10 classes there will be 100 records.

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.

tsql sql-server sql
3 Answers

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.

user_158140050

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'
user_64290720

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
)
user_13674540
Related