SQL Query in CRM 2011/2013/2015 Connection Entity

Using Advanced Find, you can get the Connection records easily



Now what if you want to run SQL Query in your SSRS Report, it might be not as easy as you did through Advanced Find because CRM does not store the Contact fields in the Connection table.
Here is the sample of SQL Query to retrieve records from Connection Entity.

select record2idname as ConnectedTo, record2roleidname as RoleTo, record2objecttypecodename as EntityRoleTo, --Account
       record1idname as ConnectedFrom, record1roleidname as RoleFrom, record1objecttypecodename as EntityRoleFrom, --Contact
       --Account fields (Connected To)
       faccount.address1_composite as OrganizationAddress1, 
       faccount.address2_composite as OrganizationAddress2,
       faccount.fax as OrganizationFax,
       faccount.telephone1 as OrganizationPhone,
       --Contact fields (Connected From)
       fcontact.address1_composite as ContactAddress1, 
       fcontact.address2_composite as ContactAddress2,
       fcontact.fax as ContactFax,
       fcontact.telephone1 as ContactPhone       
 from filteredConnection fconnection
 --make sure that only show Connection connect from Contact
 inner join FilteredContact fcontact
 on fconnection.record1id = fcontact.contactid
 and fcontact.statecode = 0 
 --make sure that only show Connection connect to Account
 inner join FilteredAccount faccount
 on fconnection.record2id = faccount.accountid
 --on fconnection.record2id = faccount.accountid
 
 --this is the criteria, I have no individual sub type field for querying GP
 --so I leave this one for you I just filter by active status
 where fconnection.statecode = 0
 -- If you want to filter only show connection 
 -- between Account and Contact please add following line, 
 -- but you need to join to other table to show all
 -- and fconnection.record1objecttypecode = 2
 --and fconnection.record2idobjecttypecode = 1

*This example is to explain the concept only, in order to retrieve Connection records between Account and Contact, you might try to tweak the query based on your requirement or feel free to ask me through the Comments.

Hope this helps!

Thanks.