I have tried to fetch a record that will return me with the doctor's ID and the total number of all the prescriptions they have given.
我试图取一条记录，用医生的身份证和他们给出的所有处方的总数返回给我。
SELECT doc.DID, COUNT(pr.DID)
FROM DOCTOR doc, PRESCRIPTION pr
WHERE doc.DID = pr.DID
GROUP BY doc.DID;
By using this statement, I am able to receive the information as long as there is at least one prescription made by a doctor. This is how my results looks like
通过使用此声明，只要医生至少有一个处方，我就能收到这些信息。这就是我的结果
DID COUNT(PR.DID)
-------------------- -------------
3292848 1
3292885 10
3293063 10
3332949 15
3332950 2
But I want it to display such that even doctors that has not prescribed before will be shown in the record with a count of 0
但是我希望它能够显示，即使是之前未规定过的医生也会在记录中显示0
DID COUNT(PR.DID)
-------------------- -------------
3292848 1
3292885 10
3293042 0
3293063 10
3332949 15
3332950 2
334021 0
1 个解决方案
#1
3
First of all, please avoid using old join syntax. Use proper JOIN syntax.
首先，请避免使用旧的连接语法。使用正确的JOIN语法。
Now here you need a LEFT JOIN which would give you everything from first table and matching records from second table. For non matching records, you will get null, which you can utilize in where or select clause.
现在你需要一个LEFT JOIN，它可以提供第一张表和第二张表中匹配记录的所有内容。对于非匹配记录，您将获得null，您可以在where或select子句中使用它。
SELECT doc.DID, COUNT(pr.DID)
FROM DOCTOR doc
left join
PRESCRIPTION pr
on doc.DID = pr.DID
GROUP BY doc.DID;
#1
3
First of all, please avoid using old join syntax. Use proper JOIN syntax.
首先，请避免使用旧的连接语法。使用正确的JOIN语法。
Now here you need a LEFT JOIN which would give you everything from first table and matching records from second table. For non matching records, you will get null, which you can utilize in where or select clause.
现在你需要一个LEFT JOIN，它可以提供第一张表和第二张表中匹配记录的所有内容。对于非匹配记录，您将获得null，您可以在where或select子句中使用它。
SELECT doc.DID, COUNT(pr.DID)
FROM DOCTOR doc
left join
PRESCRIPTION pr
on doc.DID = pr.DID
GROUP BY doc.DID;