is SOQL capable of self joins mysql style?

Question

Is it possible to do a self join with table aliasing? Lets's say I'm a book shop and I want to get all customers that purchased a book last week AND this week. In MySQL this would look somehow like this:

    SELECT Account.id from Opportunity o1, Opportunity o2    
    WHERE o1.closeDate = Last_WEEK AND o2.closeDate = This_WEEK 
    AND  o1.Account = o2.Account

What would be the equivalent in MySQL? It keeps puzzling me.

Answer 1

You can't compare one Opportunity Account Id with another in SOQL (o1.Account = o2.Account). If you try you will get the message:

Bind variables only allowed in Apex code

If instead you rework the SOQL to use a sub query you will get a different error.

SELECT AccountId from Opportunity o1  
where o1.closeDate = LAST_WEEK 
  AND o1.Account in 
  (SELECT AccountId from Opportunity o2 WHERE o2.closeDate = THIS_WEEK)

Gives:

The inner and outer selects should not be on the same object type.

You will need to either:

1. perform two queries to get the required data. You could feed the resulting Account Ids of the first query into the second query as a where clause filter.
2. retrieve the Account Ids with either close date in one SOQL query and then build up the required sets with code.

Incidentally, the Salesforce StackExchange site is a great place to ask Salesforce specific questions.