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.
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