+ Start a Discussion
kerryland6kerryland6 

soql "or" is broken?

Hi Folks,

Can anybody please explain why "or" dosn't behave as I expect in the following...?

1. Create an opportunity with a name of "Joe Bloggs"

then run this query:

select id, Account.LastName , Name
  from Opportunity where Name=  'Joe Bloggs"

you will get one result

then run this query:

select id, Account.LastName , Name
  from Opportunity where Name=  'Joe Bloggs'
or Account.Lastname = 'Bloggs'


you will get zero results.

I don't know that this Opportunity is not associated with an Account (which I assume is somehow related to the empty result), but can somebody provide a query that will work whether there is an account or not?

Thanks very much for your help -- this has driven me quite mad!

Kerry
SuperfellSuperfell
You can't, unfortunately the foreign key reference in the filter clause turns the query into an inner join from opportunity to account.
kerryland6kerryland6
So the best I can do is to write two queries...

    select id
      from Opportunity where Name=  'Joe Bloggs"

    and

    select id
      from Opportunity where Account.Lastname = 'Bloggs'


... and manually perform a union?


Thanks,
Kerry
benjasikbenjasik
yes, a manual union is your best bet today