+ Start a Discussion
Ikram Momin 16Ikram Momin 16 

I have a question regarding SOQL :

I am using below query to fetch PermissionSet assigned to users in org :
SELECT Id, PermissionSetId, AssigneeId FROM PermissionSetAssignment

And to fetch PermissionSetLicense assigned to users in org :
SELECT Id, AssigneeId, PermissionSetLicenseId FROM PermissionSetLicenseAssign

Is there any query / innerjoin or other way to fetch both((PermissionSet & PermissionSetLicense)) in single query?

OR Can I fetch both(PermissionSet & PermissionSetLicense) while fetching users ?
my users query is : 
SELECT Id, Username, LastName, FirstName FROM User

Best Answer chosen by Ikram Momin 16
RituSharmaRituSharma
You may use the below query:

Select Id,(SELECT Id, PermissionSetId, AssigneeId FROM PermissionSetAssignments),(SELECT Id, AssigneeId, PermissionSetLicenseId FROM PermissionSetLicenseAssignments) from User
 

All Answers

RituSharmaRituSharma
You may use the below query:

Select Id,(SELECT Id, PermissionSetId, AssigneeId FROM PermissionSetAssignments),(SELECT Id, AssigneeId, PermissionSetLicenseId FROM PermissionSetLicenseAssignments) from User
 
This was selected as the best answer
Ikram Momin 16Ikram Momin 16
thanks RituSharma..it was really helpful
1.can you please help me to modify above query such way to fetch Groups and check if user isFrozen ?
currently i am using, 
Group Query :SELECT UserOrGroupId,Group.Name FROM GroupMember where Group.Type='Regular' order by UserOrGroupId
isFrozen Query : SELECT UserId, IsFrozen FROM UserLogin WHERE IsFrozen = true order by UserLogin.UserId  
2. Also What would be the query if i had to fetch PermissionSetId and PermissionSetLicenseId  only and not users?
RituSharmaRituSharma
1. You may query group members in the same query as highlighted below. But you will need separate query to get isFrozen field. 

Select Id,(SELECT Id, PermissionSetId, AssigneeId FROM PermissionSetAssignments),(SELECT Id, AssigneeId, PermissionSetLicenseId FROM PermissionSetLicenseAssignments),(Select Id from GroupMemberships) from User

2. Use these queries to get details of PermissionSet and PermissionSetLicense directly:
Select Id from PermissionSet
Select Id from PermissionSetLicense
 
ruhaan Manjhiruhaan Manjhi
Ikram Momin 16Ikram Momin 16
thanks RituSHarma
1. i tried as 
Select Id,(SELECT Id, PermissionSetId, AssigneeId FROM PermissionSetAssignments),(SELECT Id, AssigneeId, PermissionSetLicenseId FROM PermissionSetLicenseAssignments),(Select Id from GroupMember) from User
but not working.
2. specifically i was asking to fetch PermissionSet and PermissionSetLicenses in single query ?