如何在SOQL中执行左连接

huangapple go评论78阅读模式
英文:

How to perform a LEFT JOIN in SOQL

问题

我有一个SQL查询,我想转换为SOQL。
我知道在SOQL中不支持LEFT JOIN。但我不知道如何在SOQL中编写这个查询。

我想要检查没有Decision__c的Cases。Case(Id)和Decision__c (Case__c)之间存在查找关系。

在SQL中,可以这样写:

Select Id
FROM Case
LEFT JOIN Decision__c D
on D.Case__c = Case.Id
WHERE Case__c IS NULL

我将所有的Cases (Case)和所有的Decisions (Decision__c)导出到Excel。通过VLOOKUP,我将Case与Decision连接起来。如果出现错误,则表示没有关联的Decision。

我在PowerQuery中导出了这些对象,并执行了左连接以合并两个查询。那些没有Decision的记录很容易被过滤掉(null值)。

所以我得到了没有Decision的Cases列表,但我想知道是否可以使用SOQL查询来获取这个列表,而不是这些额外的步骤。

英文:

I have a query in SQL that I want to convert to SOQL.
I know that a LEFT JOIN is not possible in SOQL. But I don't how to write this in SOQL.

I want to check Cases without Decision__c. There is a Lookup relation between Case(Id) and Decision__c (Case__c).

That would be in SQL:

Select Id
FROM Case
LEFT JOIN Decision__c D
on D.Case__c = Case.Id
WHERE Case__c IS NULL

I exported all Cases (Case) and all Decisions (Decision__c) to Excel. With a VLOOKLUP I connected the Case with the decision. An error = no linked decision.

I exported the objects in PowerQuery and performed a left join to merge the two queries. Those with no decision where easily filtered (null value).

So I got my list of Cases without Decision, but I want to know if I can get this list with a SOQL query, instead of these extra steps.

答案1

得分: 0

简而言之,您必须从没有 Decision__c 的情况下,选择案例,查询应该如下所示:

SELECT Id FROM Case WHERE Id NOT IN(SELECT Case__c FROM Decision__c)

虽然在 Salesforce 中我们不使用 JOINs,但可以使用几个“子查询”来帮助筛选记录。

请参考以下链接:
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select.htm

英文:

<p>To simply put it, you must, literally, select cases without Decision__c, the query should look like this:</p>

<p>SELECT Id FROM Case WHERE Id NOT IN(SELECT Case__c FROM Decision__c)</p>

<p>Although we don't JOINs in Salesforce we can use several "subqueries" to help filter records.</p>

refer to the following link:
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select.htm

huangapple
  • 本文由 发表于 2023年1月9日 17:36:02
  • 转载请务必保留本文链接:https://go.coder-hub.com/75055376.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定