返回所有用户，甚至那些不符合我条件的用户 [英] Return all Users, even those who don't fit my criteria

问题描述

我试图返回人员的完整列表，但仅在文档类型为安全清除报告"的文档类型"列中显示数据

I'm trying to return a full list of people, but only show data in the Document type column where the document type is 'Security Clearance Report'

我对SQL还是很陌生，并试图弄清楚这些东西. 目的是使管理人员可以轻松地查看谁拥有此文档类型，而谁又没有.

I'm quite new to SQL and trying to figure this stuff out. The aim is so management can easily see who has got this doc type, but also who hasn't.

SELECT papf.person_id
     , (ppnf.first_name||' '||ppnf.last_name)                                    e_name
     , dor.document_name
     , dor_type.document_type
     , TO_CHAR(dor.creation_date, 'DD/MM/YYYY')                                  dor_issue_date
FROM per_all_people_f                                         papf
     , per_person_names_f                                       ppnf
     , hr_document_types_vl                                     dor_type
     , hr_documents_of_record                                   dor
 WHERE SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
   AND papf.person_id                      = ppnf.person_id 
   AND SYSDATE BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
   AND ppnf.name_type                      = 'GLOBAL'
   AND papf.person_id                      = dor.person_id (+)
   AND dor_type.document_type_id           = dor.document_type_id
   AND dor_type.document_type = 'Security Clearance Report'
ORDER BY e_name DESC

推荐答案

如此处其他地方所述，如果您使用ANSI样式联接，则这种事情要简单得多.

As has been noted elsewhere here, this sort of thing is much simpler to do if you use ANSI style joins.

即使如此，它还是有些棘手，因为您要将LEFT JOIN的人员列表包含在HR_DOCUMENTS_OF_RECORD和HR_DOCUMENT_TYPES_VL之间的INNER JOIN中.也就是说，对于每个人，您想要的记录文件的类型为安全清除报告"(如果存在).

Even so, it is a little tricky because you want to LEFT JOIN your list of people to an INNER JOIN between HR_DOCUMENTS_OF_RECORD and HR_DOCUMENT_TYPES_VL. That is, for each person, you want the documents of record that are type "Security Clearance Report", if any exist.

这是您的操作方式:

SELECT papf.person_id
     , (ppnf.first_name||' '||ppnf.last_name)                                    e_name
     , dor.document_name
     , dor_type.document_type
     , TO_CHAR(dor.creation_date, 'DD/MM/YYYY')                                  dor_issue_date
FROM per_all_people_f  papf
INNER JOIN per_person_names_f ppnf ON ppnf.person_id = papf.person_id
           AND ppnf.name_type                      = 'GLOBAL'
           AND SYSDATE BETWEEN ppnf.effective_start_date AND NVL(ppnf.effective_end_date,SYSDATE)
LEFT JOIN ( hr_documents_of_record dor 
INNER JOIN hr_document_types_vl dor_type ON dor_type.document_type_id = dor.document_type_id
           AND dor_type.document_type = 'Security Clearance Report'  ) ON dor.person_id = papf.person_id
WHERE SYSDATE BETWEEN papf.effective_start_date AND NVL(papf.effective_end_date,SYSDATE)
ORDER BY e_name DESC;

请注意，DOR和DOR_TYPE之间的INNER JOIN在括号中，并且LEFT JOIN条件在 后面.

Note the INNER JOIN between DOR and DOR_TYPE is in parentheses and the LEFT JOIN condition is after the parentheses.

如果您只是天真地LEFT JOIN，并且任何人除了安全检查报告以外，还有记录的文件，那么您将得到太多的行.

If you just naively LEFT JOIN everything and any people have documents of record besides security clearance reports, you'll get too many rows.

这篇关于返回所有用户，甚至那些不符合我条件的用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！