Isolate the Count that comes from a LEFT JOIN independent of the WHERE clause filters

Question

Basically I want to list all the profiles name (Profile Table), each profile has a bunch of entities associated with it (Profile_Entity) and types of entities (Profile_EntityType). So, for each Profile I want to count how many entities there are on each.

This (the count) works fine If I don't filter the results. But then, If I try to filter by Entity (see if such entity belongs to a profile) it messes up my Entity Count. This happens because when the table is filtered, the rows where the EntityIDBP (serves as EntityIDBP) don't appear disappear, and the count will count the rows of the filtered table, Where I would like it to stick to the original one.

So I tried to isolate the count with a LEFT JOIN, but with no sucess.

This is what I currently have

SELECT  {Profile}.[Id],
            {Profile}.[Name],
            Count (ProfileCount.IDBP)
     FROM {Profile}
     LEFT JOIN
      ((
     /* Get all the entities that belong to a profile, trough the entity type */
        SELECT  P2.[Id] as Id,
                P2.[Name] as ProfileName,
                {Entity}.[EntityIDBP] as IDBP

         FROM    {Profile} as P2
         LEFT JOIN {Profile_EntityType} ON ({Profile_EntityType}.[ProfileId] = P2.[Id])
         LEFT JOIN    {Entity} ON ({Entity}.[EntityType_IDBP] = {Profile_EntityType}.[EntityType_IDBP] )

          UNION

        /* Get all the entities that belong to a profile directly, trough the Profile_Entity.isToInclude = 1  */
        SELECT   P2.[Id] as Id,
                    P2.[Name] as ProfileName,
                    {Entity}.[EntityIDBP] as IDBP             
         FROM    {Profile} as P2
         LEFT JOIN   {Profile_Entity} ON ({Profile_Entity}.[ProfileId] = P2.[Id] AND {Profile_Entity}.[IsToInclude] = 1)
         LEFT JOIN    {Entity} ON ({Entity}.[EntityIDBP] = {Profile_Entity}.[EntityIDBP] 

          )EXCEPT(

         /* The subquery that gets all the entities that shouldn't be accounted for the Count (Profile_Entity.isToInclude = 0)  */
         SELECT   P2.[Id] as Id,
                  P2.[Name] as ProfileName,
                  {Entity}.[EntityIDBP] as IDBP

          FROM    {Profile} as P2
          JOIN   {Profile_Entity} ON ({Profile_Entity}.[ProfileId] = P2.[Id] AND {Profile_Entity}.[IsToInclude] = 0)
          JOIN    {Entity} ON ({Entity}.[EntityIDBP] = {Profile_Entity}.[EntityIDBP] ))) as ProfileCount ON ({Profile}.[Id] = ProfileCount.Id)

WHERE ProfileCount.IDBP IN (301000044)  
/* The Filter used to know if a profile has a entity or not ; Right now it's a fixed value just to demonstrate*/ 
/*The 301000044 represents the Entity IDBP */

GROUP BY {Profile}.[Name],{Profile}.[Id])

For the example here are the data model tables. Profile table:

|---------------------|------------------|
|Id  |    Name        |     (...)        |
|---------------------|------------------|
|10  | Profile1       |        (...)     |
|---------------------|------------------|

Profile_Entity table:

|---------------------|------------------|-----------------------------|
|      ProfileId      |     EntityIDBP   |isToInclude                  |
|                     |serves as the     |/*this representes wheter the| 
|                     |unique id         | entity should be considered | 
|                     |                  |  for the count (=1) or not  |
|                     |                  |   (=0) */                   |
|---------------------|------------------|-----------------------------|
|     10              |       301000044  | 1                           |
|---------------------|------------------|-----------------------------|
|                     |                  |                             |
|     10              |       301000045  | 1                           |
----------------------|------------------|-----------------------------| 
|     10              |       301000043  | 0 /* goes into the EXCEPT   |
|                     |                  |         clause */           |
|---------------------|------------------|   /*thus the EXCEPT clause*/|

Profile-EntityType table:

|---------------------|------------------|
|Id  |EntityType_IDBP |     (...)        |
|---------------------|------------------|
|10  | ProfileType    |          -----   |
|---------------------|------------------|

/*Then on the EntityTable  I would have all the Entities that belong to this 
type and aggregate them all together. Let's imagine it's 10 */

Entity Table

|---------------------|------------------|
|Id  |    EntityIDBP  | EntityType_IDBP  | /* 10 entities (records) with this 
|                     |                  |   TypeCod */
|---------------------|------------------|
|10  | IDBP           |      ProfileType | 
|---------------------|------------------|

The expected result:

|---------------------|------------------|
|Id  |    ProfileName |     EntityCount  |
|---------------------|------------------|
|10  | Profile1       |        11        |
|---------------------|------------------|

The count is 11 because there are two (2) entities with isToInclude = 1 on Profile_Entity table minus 1 entity from Profile_Entity with isToInclude = 0 (the Except clause) plus 10 enties with that type.

Obs. The syntax may be a little bit different than what you are used to because this is done in a Outsystems platform.

Add some sample table data and the expected result - all as formatted text, not images. Take a look at https://stackoverflow.com/help/mcve first. — jarlh, Dec 12 '18 at 11:51
**Add the expected result** In table form, preferably with a **precise** explanation why that is what you expect for each particular result row. Just saying "the excepted result would be ..." is unlikely to help much. — Erwin Smout, Dec 12 '18 at 14:09
Tried to better explain myself. Aswell as deleting stuff from the query not needed in this specific question — user3336801, Dec 12 '18 at 22:03
You have two identical rows in Profile_Entity. That's nonrelational and semantically troubling to boot. Furthermore, it seems to me that you want to take one of these identical rows and associate it with your 10 "entities in Entity table", then count those 10 "entities in entity table", and then add to that the number of identical rows in Profile_Entity that were not matched. Seems like apples and oranges to me, but never mind : it's two distinct counts (two distinct aggregations) and that needs to show in how the query is written. I think you need to search in that direction. — Erwin Smout, Dec 13 '18 at 08:15
Those identical rows in Profile-Entity were miswritten (they were supposed to be different), but I'll try to look into the direction you tried to explain. Thanks for the help — user3336801, Dec 13 '18 at 09:08

score 0 · Accepted Answer · answered Dec 13 '18 at 23:37

Ended up using the temporary table I get to retrieve all the entities from a Profile (The Union and the Except) as a Condition on this same query, where the only difference is that I feed this second one the IDBP of the entity I want to filter by. So I have something like this

SELECT A.ProfileName, A.ProfileId, Count(A.IDBP)
 FROM (
       SELECT 'all entities IDBP associated with a profile, as well as its Id and Name' as A
       WHERE A.IDBP IN (A WHERE Entity.IDBP = 'xxxx')
      )

This preserves the Count and does the filtering

Isolate the Count that comes from a LEFT JOIN independent of the WHERE clause filters

1 Answers1