By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Document_type (DocId int, DocName VARCHAR (100));
INSERT INTO Document_type (DocId, DocName) VALUES
(1, 'General Document'),
(2, 'Radiology'),
(3, 'Billing Document'),
(4, 'Insurance Document'),
(5, 'Lab Report');
CREATE TABLE visitdocs(vdocid INT, DocId int, visitno int);
INSERT INTO visitdocs (vdocid, DocId, visitno) VALUES
(1, 1, 1);
CREATE TABLE visittbl(visitno INT, vdocid int, pvisitno int);
INSERT INTO visittbl (visitno, vdocid, pvisitno) VALUES
(1, 1,1001);
7 rows affected
select t3.pvisitno, t1.DocName, count(t2.vdocid) as [count]
from Document_type t1
left outer join visitdocs t2 on t2.DocId = t1.DocId
left outer join visittbl t3 on t3.visitno = t2.visitno and t3.visitno=1
group by t3.pvisitno,t1.DocName
order by count(t2.vdocid) desc
| pvisitno | DocName | count |
|---|---|---|
| 1001 | General Document | 1 |
| null | Insurance Document | 0 |
| null | Lab Report | 0 |
| null | Radiology | 0 |
| null | Billing Document | 0 |
Warning: Null value is eliminated by an aggregate or other SET operation.