SQL Studio

SELECT
SELECT 
[TOP  number] 
expression [ [AS] output_name ] [, ...] ]
[FROM entity_logicalname [alias] [{INNER | LEFT} JOIN ...] ]
[ WHERE <search_condition> ] 
[ ORDER BY expression [ ASC | DESC ]]
  • To reduce load on the server, the TOP defaults to 10
  • When specifing fields, a 3 part expression (alias.field.name) is supported to display the name of lookups or option sets. This requires that the entity_logalname have an alias specified.
  • When a from clause contains inner or left joins, this is being converted into MSCRM Query Expressions. So the fields that can be used as criteria are only those availble on the two connecting entities.
Examples
select top 20 contactid, fullname, contact.statecode.name
from contact 
where 
(statecode=0 and firstname is not null)
select c.contactid, ownerid, c.fullname, a.name 
from
contact as c
inner join account as a on c.parentcustomerid = a.accountid
where c.fullname like 'Bob%'
order by ownerid desc, fullname