SailPoint Identity IQ JDBC Aggregation: A common mistake

 A common mistake that I have seen being made is how the aggregation query is written. 

Let's say there are three tables:

1. User
2. Groups
3. Membership

The names are exactly what is contained in the tables, User table contains the user data, Groups table contains the group data, Membership table contains the membership data.

Pay attention to both the queries, both will return the result and mostly pass the unit testing. The issue is that if a user is not part of any groups, they would not appear in the result set of query1

Incorrect Query

SELECT User.*, Membership.groupName 

FROM User, Membership 

WHERE User.Id = Membership.UserId;

Correct Query

SELECT User.*, Membership.groupName

FROM User

LEFT JOIN Membership 

ON User.Id = Membership.UserId;

Hope it helps !!