I have aggregate with two table join. Lets say table A and table B. Table b.
Table A will have only one record and table B will have many records reference to table A
Table B record has status active, inprogress. And my filter will be status = active.
Lets say I have 2 records in table B with one active and one inprogress status.
What I need is record only which doesnt have record with status in progress from table B.
Note: Currently i get output from table B since it met active status.
Hi @Touseef Ahmed ,
I'm not entirely sure i understand the requirement, so this is what i think you are asking :
You want to retrieve records from Table A, but only those that don't have a record in Table B with status InProgress ? Regardless of any other records they might or might not have in Table B ?
In that case, you are looking for what would typically be a 'where not exists' subquery in SQL. You can achieve this by looking for the unwanted status in the join condition (a with or without join) and then filtering on TableB identifier being nullidentifier.
Dorine
So, trying to show in pseudo code
Aggregate
Sources tab
TableA With Or Without TableB
TableA.Id = TableB.TableAId AND TableB.Status = InProgress
Filter tab
TableB.Id = Nullidentifier
Hii @Touseef Ahmed
we can solved this by joining the child table (TableB) twice.
First, I used an Only With join to get the TableB record with Status = "Active".
Then I joined TableB again as TableB_InProgress using a With or Without join, with the condition:
TableA.Id = TableB_InProgress.TableA AND TableB_InProgress.Status = "InProgress"
Finally, I added the following filter:
TableB_InProgress.Id = NullIdentifier()
This way, the Aggregate returns the TableA record only when an Active TableB record exists and there is no related TableB record with InProgress status.
The With or Without join is important because it allows the TableA record to remain when no InProgress record exists, and the NullIdentifier() check removes the records where an InProgress child is found.