SQL – Inner Join on Contacts and OptionSetMetadata tables


Using the Data Export Service, you can also query metadata to join customer data into a single flat table. For those trying to get OptionSetMetadata labels instead of code, here is a short SQL snippet to get you started.

select Id as contactguid, 
address1_line1 as address, 
from dbo.contact

INNER JOIN dbo.OptionSetMetadata ON dbo.contact.gendercode=dbo.OptionSetMetadata.[Option] AND dbo.OptionSetMetadata.OptionSetName = 'gendercode'

This is great when you want to create a single customer view normalized with all other associated customer data.

Leave a comment