I'm using a SOQL query in an ETL tool to extract Account data from Salesforce and ultimately map it to our ERP system. The business has come up with two different use cases for extracting data in the same integration pipeline. Below is the query I'm currently using and below it is the additional scenario. I'd like to combine these into one query, if possible. They're similar, but the first excludes the integration user (_p_integrationUserId) and the second is based on a change made by the integration user. Also, the second includes an inner join on the Account History object.
Current Query
SELECT
Industry, OwnerId, Category__c, Name, Phone, Website, BillingCity,
BillingCountry, BillingState, BillingStateCode, BillingPostalCode,
BillingStreet, Id, NetSuite_Internal_ID__c, LastModifiedDate,
Owner.NS_Internal_ID__c, UUID__c, BD_Partner__c, Brand__r.Name,
NS_Address_Internal_Id__c, Type, Ad_Agency_Parent__c, Address_Changed__c,
lastmodifiedby.Name, PM_Business_Alias__c,
Ecomm_Retention_Rep_NS_Internal_ID__c
FROM Account
WHERE (LastModifiedDate= last_n_days:1
AND lastmodifiedbyid NOT IN ('" + _p_integrationUserId + "')
AND NetSuite_Internal_ID__c<>null
AND Type='" + _p_Customer_Type +"')
AND RecordTypeId NOT IN ('" + _p_Location_RT + "','" + _p_Production_Shell_RT + "')
Addt'l Query
SELECT
Industry, OwnerId, Category__c, Name, Phone, Website,
BillingCity, BillingCountry, BillingState, BillingStateCode,
BillingPostalCode, BillingStreet, Id, NetSuite_Internal_ID__c,
LastModifiedDate, Owner.NS_Internal_ID__c, UUID__c, BD_Partner__c,
Brand__r.Name, NS_Address_Internal_Id__c, Type, Ad_Agency_Parent__c,
Address_Changed__c, lastmodifiedby.Name, PM_Business_Alias__c,
Ecomm_Retention_Rep_NS_Internal_ID__c, (SELECT Id, CreatedById FROM
Histories WHERE Field = 'Owner')
FROM Account WHERE (LastModifiedDate= last_n_days:1
AND lastmodifiedbyid IN ('" + _p_integrationUserId
+ "')
AND NetSuite_Internal_ID__c<>null
AND Type='" + _p_Customer_Type +"')
AND RecordTypeId not in ('" + _p_Location_RT + "','" + _p_Production_Shell_RT + "')
I can always have two separate queries and then join the results, but it'd be great if I can combine these into one.
Updated Query
SELECT
--A bunch of Account fields--,
(SELECT Id,
FROM AccountHistory
WHERE (Field IN ('Name', 'Industry', 'Phone', 'Website', 'OwnerId',
'Category__c', 'BillingStreet', 'BillingCity',
'BillingState', 'BillingPostalCode', 'BillingCountry',
'UUID__c', 'BD_Partner__c', 'Brand__c', 'Type',
'Ad_Agency_Parent__c', 'PM_Business_Alias__c')
AND CreatedDate = last_n_days:1))
FROM Account
WHERE (NetSuite_Internal_ID__c<>null
AND Type='" + _p_Customer_Type +"'
AND RecordTypeId not in ('" + _p_Location_RT + "','" + _p_Production_Shell_RT + "'))