SQL Select Count Subquery, Joins messing up everything-CodePudding

I've just recently started at a new job and I'm way too lost in everything. I've got a task to work with 4 different tables. I think I've got the "logic" correct, but I think I'm failing on joining the various separately working things together.

The Case somehow returns two rows when the comparison is true, if it isnt, it displays (correctly) just one. Works fine without joins.
The count subq works when by itself, but when I'm trying to tie it together, it displays anything from showing the same number everywhere or displaying far too large numbers (likely multiples or
multiples).

Select  Distinct RPD_PERSONS.PERSON_ID "id",
        RPD_PERSONS.SURN_TXT ||' '|| RPD_PERSONS.NAME_TXT "Name",
        Case ADD_ROLE_PERS.ROLE_CODE When 'Manager'
            Then 'yes'
            Else 'no'
            End "Manager",
        (
                Select Count(LDD_CERTS.Cert_ID)
                From LDD_CERTS
                Join LDD_PERS_CERTS
                    On LDD_PERS_CERTS.CERT_ID = LDD_CERTS.CERT_ID
                Where MONTHS_BETWEEN(LDD_CERTS.VALID_TO,SYSDATE)>0
                    And LDD_PERS_CERTS.CERT_CHANGE_TYPE>=0
        ) "no. of certificates"
    From RPD_PERSONS
    Join ADD_ROLE_PERS
        On ADD_ROLE_PERS.Person_ID = RPD_PERSONS.Person_ID
    Where RPD_PERSONS.Partic_ID = 1
    Group By RPD_PERSONS.PERSON_ID, RPD_PERSONS.SURN_TXT ||' '|| RPD_PERSONS.NAME_TXT, ADD_ROLE_PERS.ROLE_CODE
    Order By RPD_PERSONS.Person_ID;

This is the subq that, by itself, seems to work perfectly.

Select LDD_PERS_CERTS.PERSON_UID,Count(LDD_CERTS.Cert_ID)
            From LDD_CERTS
            Join LDD_PERS_CERTS
                ON LDD_PERS_CERTS.CERT_ID = LDD_CERTS.CERT_ID
            Where MONTHS_BETWEEN(LDD_CERTS.VALID_TO,SYSDATE)>0
                AND LDD_PERS_CERTS.CERT_CHANGE_TYPE>=0
            Group By LDD_PERS_CERTS.PERSON_UID
            order by LDD_PERS_CERTS.PERSON_UID;

CodePudding user response：

You have a lot of things going on although a short query to get it, but let me try to summarize what I THINK you are trying to get.

You want a list of distinct people within the company with a count of how many ACTIVE certs (not expired) per person. From that, you also want to know if they are in a management position or not (via roles).

Q: For a person who may be a manager, but also an under-manager to a higher-up, do you want to see that person in both roles as typical business structures could have multiple layers of management, OR... Do you only care to see a person once, and if they are a manager OR some other level. What if a person has 3 or more roles, do you want to see them every instance? If your PRIMARY care is Manager Yes or No, the query gets even more simplified.

Now, your query of counts for valid certs. The MONTHS_BETWEEN() function appears to be you are running in Oracle. Based on the two parameters of the Valid_To date compared to sysdate is an indication that the valid to is always intended to be in the future (ie: Still has an active cert). If this is the case, you will not be able to optimize query as function calling is not Sargable Instead, you should only have to do where Valid_To > SysDate, in other words, only those that have not yet expired. You MIGHT even be better served by pre-aggregating all counts of still active cert counts per Cert ID, then joining to the person certs table since the person cert check is for all where the cert_change_type >= 0 which could imply ALL. What condition would a Cert_Change_Type be anything less than zero, and if never, that where clause is pointless.

Next, your SELECT DISTINCT query needs a bit of adjustments. Your column-based select has no context to the outer person ID and is just aggregating the total certs. There is no correlation to the person ID to the certs being counted for. I can only GUESS that there is some relationship such as

RPD_Persons.Person_id =  LDD_Pers_Certs.Person_UID

Having stated all that, I would have the following table/indexes

table              index
LDD_PERS_CERTS     ( CERT_CHANGE_TYPE, PERSON_UID, CERT_ID )
LDD_CERTS          ( valid_to, cert_id )
RPD_PERSONS        ( partic_id, person_id, surn_txt, name_txt )
ADD_ROLE_PERS      ( person_id, role_code )

I would try something like

Select 
        lpc.PERSON_UID,
        ValCerts.CertCount
    From 
        ( select
                Cert_id,
                count(*) CertCounts
            from
                LDD_CERTS
            where
                Valid_To > sysDate
            group by
                Cert_id ) ValCerts
            JOIN LDD_PERS_CERTS lpc
                on ValCerts.Cert_id = lpc.cert_id
    Where 
        lpc.CERT_CHANGE_TYPE >= 0

Now, if you only care if a given person is a manager or not, I would pre-query that only as you were not actually returning a person's SPECIFIC ROLE, just the fact they were a manager or not. My final query might look like'

select distinct
        p.PERSON_ID id,
        p.SURN_TXT || ' ' || p.NAME_TXT Name,
        Case when arp.Person_id IS NULL
            then 'no' else 'yes' end Manager,
        coalesce( certs.CertCount, 0 ) ActiveCertsForUser
    from
        RPD_PERSONS p
            Join ADD_ROLE_PERS arp
                On p.Person_ID = arp.Person_ID
                AND arp.role_code = 'Manager'
            LEFT JOIN
            (   Select 
                        lpc.PERSON_UID,
                        ValCerts.CertCount
                    From 
                        ( select
                                Cert_id,
                                count(*) CertCounts
                            from
                                LDD_CERTS
                            where
                                Valid_To > sysDate
                            group by
                                Cert_id ) ValCerts
                            JOIN LDD_PERS_CERTS lpc
                                on ValCerts.Cert_id = lpc.cert_id
                                AND lpc.CERT_CHANGE_TYPE >= 0 )
            ) Certs
                on p.Person_id = Certs.Person_uid
    Where 
        p.Partic_ID = 1

Now, if the p.partic_id = 1 represents only 1 person, then that wont make as much sense to query all people with a given certificate status, etc. But if Partic_id = 1 represents a group of people such as within a given association / division of a company, then it should be fine.

Any questions, let me know and I can revise / update answer

CodePudding user response：

CASE issue: there can be, presumably, be multiple records in ADD_ROLE_PERS for each person. If a person can have two or more roles running concurrently then you need to decide what the business logic is that you need to use to handle this. If a person can only have one active role at a time presumably there is a "active/disabled" column or effective date columns you should be using to identify the active record (or, potentially, there is a data issue).
The subquery should return the same value for every single row in your resultset, as it is completely isolated/standalone from your main query. If you want it to produce counts that are relevant to each row then you will need to connect it to the tables in the main table (look up correlated subqueries if you don't know how to so this)