I am trying to write the query to retrieve the data from the Contact table. So, let us say I want the following fields
FirstName, LastName and Gender.
Now, the field for gender is gendercode, which is an option set and this data resides in StringMapBase.
What would the query to return the text value of gendercode?
I could write this, but surely there is a better way to write.
SELECT FirstName, LastName, GenderCode FROM Contact
SELECT FirstName, LastName, GenderCode, sm.Value as Gender FROM Contact c, StringMap sm
WHERE c.GenderCode = sm.AttributeValue
AND sm.AttributeName = 'gendercode'
Use the filtered views for reading from the DB. Fully supported and gives you what you need without jumping through hoops and creating joins.
SELECT firstname, lastname, gendercodename from FilteredContact