When writing a SQL with several joins, at some point the query stopped returning any rows. The joins "failed" silently with no error. The joins did not fail because the data did not support the join, as the example shows. Re-writing as two parts using nested SQL avoids the problem. It does not seem to be an issue of the number of joins used.
– 1) this works and returns three rows
select t0.id as oppo_id, t0.name, t0.OWNERID, t1.role from sforce.opportunity t0
join sforce.OPPORTUNITYCONTACTROLE t1 on t0.id = t1.OPPORTUNITYID
where t0.id = '0064100000K4xVKAAZ'
--OPPO_ID NAME OWNERID ROLE
--0064100000K4xVKAAZ SomeCompany 005410000036bQSAAY Seller Counsel
--0064100000K4xVKAAZ SomeCompany 005410000036bQSAAY Seller Counsel
--0064100000K4xVKAAZ SomeCompany 005410000036bQSAAY Seller Counsel
– 2) this works and returns a lastname from user
select t0.id as oppo_id, t0.name, t0.OWNERID, t2.LASTNAME from sforce.opportunity t0
join sforce.USER t2 on t0.ownerid = t2.id
where t0.id = '0064100000K4xVKAAZ'
--OPPO_ID NAME OWNERID LASTNAME
--0064100000K4xVKAAZ SomeCompany 005410000036bQSAAY Martin
– 3) *** FAILS: this returns no rows at all and has no error
select t0.id as oppo_id, t0.name, t0.OWNERID, t1.role, t2.LASTNAME from sforce.opportunity t0
join sforce.OPPORTUNITYCONTACTROLE t1 on t0.id = t1.OPPORTUNITYID
join sforce.USER t2 on t0.ownerid = t2.id
where t0.id = '0064100000K4xVKAAZ'
– 4) this works and returns 3 rows with all columns populated
select t3.oppo_id, t3.oppo_name, t3.ownerid, t3.role, t4.lastname
from
( select t0.id as oppo_id, t0.name as oppo_name, t0.OWNERID, t1.role from sforce.opportunity t0
join sforce.OPPORTUNITYCONTACTROLE t1 on t0.id = t1.OPPORTUNITYID
where t0.id = '0064100000K4xVKAAZ'
) t3
join sforce.user t4 on t3.ownerid = t4.id
--OPPO_ID OPPO_NAME OWNERID ROLE LASTNAME
--0064100000K4xVKAAZ SomeCompany 005410000036bQSAAY Seller Counsel Martin
--0064100000K4xVKAAZ SomeCompany 005410000036bQSAAY Seller Counsel Martin
--0064100000K4xVKAAZ SomeCompany 005410000036bQSAAY Seller Counsel Martin
