Best-practice samples for common queries
See the following samples of best practices for some common use cases.
- Get a list of
Subscriptions
and the associatedAccounts
whose custom fieldgeographicregion__c
isWest
SELECT subscription.id, account.id FROM Subscription INNER JOIN account ON subscription.accountid = account.id WHERE account.geographicregion__c = ‘West’
- Get the
Products
updated since January 1, 2019SELECT * FROM Product WHERE updateddate >= timestamp '2019-01-01 -7:00'
- Get the
Accounts
with Usage for theProducts
with specified SKUsSELECT a.accountnumber FROM Usage u, Account a, Product p WHERE a.accountnumber = u.accountnumber AND p.sku IN ( 'SKU-200001', 'SKU-200002', 'SKU-200003', 'SKU-200004' ) GROUP BY a.accountnumber
- Get
Attachments
for the object by specifying the associated object ID and associated object type in WHERE clauseSELECT filename, fileid, filecontenttype FROM attachment WHERE associatedobjecttype = 'account' AND associatedobjectid = 'ff808081298c6e5401298c76f28b006c'
- Get a list of
Invoices
whose invoice due date is later than the date of current UTC in Los Angeles time zone.
For more information about thedate
function, see Get the date value of a timestamp.SELECT * FROM invoice WHERE duedate >= date(CURRENT_TIMESTAMP AT TIME ZONE 'America/Los_Angeles')