Proper Use of ORACLE LEFT JOIN-CodePudding

I have the following query:

SELECT eps.PROPOSAL_NUMBER,
       pers.PROP_PERSON_ROLE_ID,
       pers.FULL_NAME,       
       pers.HOME_UNIT
       FROM EPS_PROPOSAL eps
       FULL OUTER JOIN EPS_PROP_PERSON pers USING (PROPOSAL_NUMBER)
       WHERE (pers.PROP_PERSON_ROLE_ID = 'PI' OR 
              pers.PROP_PERSON_ROLE_ID = 'PD' OR 
              pers.PROP_PERSON_ROLE_ID IS NULL); -- 4483

However, if I run the following:

SELECT COUNT (*) PROPOSAL_NUMBER FROM EPS_PROPOSAL; -- 4504

That's a difference of 21.

I went and took a look at the why the difference and sure enough, 21 of the proposals in the EPS_PROPOSAL table do not have any people in the EPS_PROP_PERSON table with either a PI or PD role.
It is only possible to have a single person designated as either a PI or PD per proposal (e.g. there will never be situation where more than one PI/PD would exist per PROPOSAL_NUMBER); however, you could have multiple other roles there (I could list them here, but its irrelevant to the question at hand).

How do I modify my query so that it will return the PROPOSAL_NUMBER from EPS_PROPOSAL and insert NULL values for the pers columns for those proposals where a proposal does not have a PI or PD?

I thought that this would be related to the JOIN, but using LEFT JOIN is not helping here.

My LEFT JOIN attempt:

SELECT eps.PROPOSAL_NUMBER,
       pers.PROP_PERSON_ROLE_ID,
       pers.FULL_NAME,       
       pers.HOME_UNIT
       FROM EPS_PROPOSAL eps
       LEFT JOIN EPS_PROP_PERSON pers USING (PROPOSAL_NUMBER)
       WHERE (pers.PROP_PERSON_ROLE_ID = 'PI' OR 
              pers.PROP_PERSON_ROLE_ID = 'PD' OR 
              pers.PROP_PERSON_ROLE_ID IS NULL); -- 4483

CodePudding user response：

From your explanations in the comments, I believe this is what you are looking for. I am going to use the union operator to combine two like datasets together. One will be your original query for PI and PD and the other I will do an inverse of your predicate. In the bottom query, I am just going to assign NULL as the values from pers, as default.

SELECT DISTINCT
  PROPOSAL_NUMBER, 
  FIRST_VALUE(PROP_PERSON_ROLE_ID) OVER (PARTITION BY PROPOSAL_NUMBER ORDER BY PROPOSAL_NUMBER), 
  FIRST_VALUE(FULL_NAME) OVER (PARTITION BY PROPOSAL_NUMBER ORDER BY PROPOSAL_NUMBER), 
  FIRST_VALUE(HOME_UNIT) OVER (PARTITION BY PROPOSAL_NUMBER ORDER BY PROPOSAL_NUMBER)
FROM
((
SELECT eps.PROPOSAL_NUMBER,
       pers.PROP_PERSON_ROLE_ID,
       pers.FULL_NAME,       
       pers.HOME_UNIT
       FROM EPS_PROPOSAL eps
       LEFT JOIN EPS_PROP_PERSON pers ON eps.PROPOSAL_NUMBER = pers.PROPOSAL_NUMBER
       WHERE (pers.PROP_PERSON_ROLE_ID = 'PI' OR 
              pers.PROP_PERSON_ROLE_ID = 'PD' OR 
              pers.PROP_PERSON_ROLE_ID IS NULL)
)
UNION
(
SELECT eps.PROPOSAL_NUMBER,
       NULL as PROP_PERSON_ROLE_ID,
       NULL as FULL_NAME,       
       NULL as HOME_UNIT
       FROM EPS_PROPOSAL eps
       LEFT JOIN EPS_PROP_PERSON pers ON eps.PROPOSAL_NUMBER = pers.PROPOSAL_NUMBER
       WHERE (pers.PROP_PERSON_ROLE_ID != 'PI' AND
              pers.PROP_PERSON_ROLE_ID != 'PD' AND
              pers.PROP_PERSON_ROLE_ID IS NOT NULL)
))

CodePudding user response：

One or both of the comments are definitely correct since - rest assured - your join syntax is correct. The question you'll need to answer is what the filtering conditions are doing to your result. Background: oracle 8i and 9i certified dba and anyways 15 years of oracle .