Multiple Nested Inner Joins: not all records are shown

Question

I have difficulty joining two tables that look like the following:

• The main table PMEOBJECT which has a unique key named OBJECTID and has in total 12768 rows.
• Then I want to join PMEOBJECTVALIDITY on it which has an n:1 relationship with PMEOBJECT, since it has more rows, because it saves the changes over time of PMEOBJECT (i.e. when a certain object is not valid anymore), this one has 12789 rows (meaning only 21 objects changed over time). However, I only want to have the current last VALIDFROM date shown in the query. This all works fine.

• Then the trouble starts when I want to join PMEOBJECTDIMENSION, which has an n:1 relationship with PMEOBJECTVALIDITY and has 36737 rows in total.

          SELECT 
              PMEOBJECT.OBJECTID
              ,PMEOBJECTVALIDITY.VALIDFROM
              ,PMEOBJECTDIMENSION.DIMENSION2_
          FROM PMEOBJECT
                  LEFT JOIN PMEOBJECTVALIDITY
                      ON PMEOBJECTVALIDITY.OBJECTID = PMEOBJECT.OBJECTID
                      AND PMEOBJECTVALIDITY.DATAAREAID = PMEOBJECT.DATAAREAID
                      INNER JOIN(
                          SELECT 
                              OBJECTID,
                              MAX(VALIDFROM) AS NEWFROMDATE,
                              MAX(VALIDTO) AS NEWTODATE
                          FROM PMEOBJECTVALIDITY B
                          GROUP BY OBJECTID
                          ) B 
                          ON PMEOBJECTVALIDITY.OBJECTID = B.OBJECTID 
                          AND PMEOBJECTVALIDITY.VALIDFROM = B.NEWFROMDATE
                  LEFT JOIN PMEOBJECTDIMENSION
                      ON PMEOBJECTDIMENSION.OBJECTVALIDITYID = PMEOBJECTVALIDITY.RECID
                      AND PMEOBJECTDIMENSION.DATAAREAID = PMEOBJECTVALIDITY.DATAAREAID
                      INNER JOIN(
                          SELECT 
                              OBJECTVALIDITYID,
                              MAX(VALIDFROM) AS NEWFROMDATE_2
                          FROM PMEOBJECTDIMENSION C
                          GROUP BY OBJECTVALIDITYID
                          ) C
                          ON PMEOBJECTDIMENSION.OBJECTVALIDITYID = C.OBJECTVALIDITYID 
                          AND PMEOBJECTDIMENSION.VALIDFROM = C.NEWFROMDATE_2
  

Results in query per step:

• SELECT PMEOBJECT: 12768 rows
• LEFT JOIN PMEVALIDITY: 12789 rows
• INNER JOIN PMEVALIDITY: 12768 rows
• LEFT JOIN PMEOBJECTDIMENSION: 36737 rows
• INNER JOIN PMEOBJECTDIMENSION: 12729 rows

I want the end result again to have the same 12768 rows, I don't want any ObjectId to be left out.

What am I missing here?

Kind regards, Igor

Answer 1

The INNER JOINs are filtering out records- what you want is that the LEFT JOIN table (PMEOBJECTVALIDITY and PMEOBJECTDIMENSION) should only include records that have at least a match on the INNER JOIN queries (alias B and C). You can accomplish this with by nesting the INNER JOIN with the LEFT JOIN, generally done as follows:

SELECT *
FROM A
LEFT JOIN B
    INNER JOIN C
    ON B.ID = C.BID
ON A.ID = B.AID

Now B is INNER JOINed on C and will only contain records that have a match in C, but will preserve the LEFT JOIN not remove any records from A.

In your case, you can simply move the ON clause from the LEFT JOIN to the end of the following INNER JOIN.

    SELECT 
        PMEOBJECT.OBJECTID
        ,PMEOBJECTVALIDITY.VALIDFROM
        ,PMEOBJECTDIMENSION.DIMENSION2_
    FROM PMEOBJECT
            LEFT JOIN PMEOBJECTVALIDITY
                INNER JOIN(
                    SELECT 
                        OBJECTID,
                        MAX(VALIDFROM) AS NEWFROMDATE,
                        MAX(VALIDTO) AS NEWTODATE
                    FROM PMEOBJECTVALIDITY B
                    GROUP BY OBJECTID
                    ) B 
                    ON PMEOBJECTVALIDITY.OBJECTID = B.OBJECTID 
                    AND PMEOBJECTVALIDITY.VALIDFROM = B.NEWFROMDATE
            ON PMEOBJECTVALIDITY.OBJECTID = PMEOBJECT.OBJECTID
                AND PMEOBJECTVALIDITY.DATAAREAID = PMEOBJECT.DATAAREAID --here it is!
            LEFT JOIN PMEOBJECTDIMENSION                    
                INNER JOIN(
                    SELECT 
                        OBJECTVALIDITYID,
                        MAX(VALIDFROM) AS NEWFROMDATE_2
                    FROM PMEOBJECTDIMENSION C
                    GROUP BY OBJECTVALIDITYID
                    ) C
                    ON PMEOBJECTDIMENSION.OBJECTVALIDITYID = C.OBJECTVALIDITYID 
                    AND PMEOBJECTDIMENSION.VALIDFROM = C.NEWFROMDATE_2
           ON PMEOBJECTDIMENSION.OBJECTVALIDITYID = PMEOBJECTVALIDITY.RECID
                AND PMEOBJECTDIMENSION.DATAAREAID = PMEOBJECTVALIDITY.DATAAREAID --I'm here

Answer 2

Following might help:

from PMEOBJECTDIMENSION onwards:

   LEFT JOIN (SELECT PMEOBJECTDIMENSION.OBJECTVALIDITYID, PMEOBJECTDIMENSION.DATAAREAID
            FROM PMEOBJECTDIMENSION
            INNER JOIN(SELECT OBJECTVALIDITYID, MAX(VALIDFROM) AS NEWFROMDATE_2 
                FROM PMEOBJECTDIMENSION C 
                GROUP BY OBJECTVALIDITYID
                ) C
            ON PMEOBJECTDIMENSION.OBJECTVALIDITYID = C.OBJECTVALIDITYID 
            AND PMEOBJECTDIMENSION.VALIDFROM = C.NEWFROMDATE_2
        )X
        ON X.OBJECTVALIDITYID = PMEOBJECTVALIDITY.RECID
    AND X.DATAAREAID = PMEOBJECTVALIDITY.DATAAREAID

and select the distinct records if duplicates present.