CODEWITHSUNDEEP
sqlsql-serverjoinsubquery
Nikhila Bakurupanda
Nikhila Bakurupanda

Reputation: 21

Can this subquery be written as a join and still get the same result set/number of rows?

How do I write the below as a join and get the same number of rows?

SELECT
  s.subjectid,
  s.subjectname,
  (SELECT
    COUNT(*)
  FROM dbo.Classes AS c
  WHERE c.SubjectID = s.SubjectID
  AND c.MondaySchedule = 1)
  AS numofclasses
FROM dbo.subjects AS s
ORDER BY numofclasses DESC

I am trying to write it like below but getting a different answer: 

SELECT
  s.subjectid,
  COUNT(ClassID) AS numberofclasses
FROM dbo.subjects AS s
LEFT JOIN dbo.classes AS c
  ON s.SubjectID = c.SubjectID
WHERE c.MondaySchedule = 1
GROUP BY s.Subjectid
ORDER BY numberofclasses DESC

Upvotes: 0

Views: 52

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270633

Move the where condition to the on condition. It is converting the outer join to an inner join:

select s.subjectid, count(ClassID) as numberofclasses
from dbo.subjects s left join
     dbo.classes c
     on s.SubjectID = c.SubjectID and c.MondaySchedule = 1 
group by s.Subjectid
order by numberofclasses desc ;

This does assume that subjects(subjectid) is unique (or a primary key). If not, the two might return different results.

Upvotes: 1

Related Questions