1

I'm extracting the "Id", "CaseNumber", "ContactId" & "ParentId" variables from "Case" object:

SELECT Id, CaseNumber, ContactId, ParentId FROM Case
Id CaseNumber ContactId ParentId
5004V000000000000A 00000001 003300000000000000 #N/A
5004V000000000000B 00000002 003300000000000000 5004V000000000000A

But instead of display the ID string of the "ParentId", I need to display the CaseNumber whose the "Id" of corresponding "ParentId" belongs to.

Using the above example as a reference, by replacing the "ParentId" by the ParentCaseNumber the desired result would be:

Id CaseNumber ContactId ParentCaseNumber
5004V000000000000A 00000001 003300000000000000 #N/A
5004V000000000000B 00000002 003300000000000000 00000001

Additionally, instead of displaying the "ContactId" I need to display the ContactName. I know there is a "Name" variable inside the "User" object that matches the "ContactId" provided by "Case" object query.

"User" object query example:

SELECT Id, ContactId, Name FROM User`
Id ContactId Name
005300000000000000 003300000000000000 John Smith

So I need to replace "ContactId" returned by the "Case" object query by the "Name" found on "User" object as above. The desired query output result would be:

Id CaseNumber ContactName ParentCaseNumber
5004V000000000000A 00000001 John Smith #N/A
5004V000000000000B 00000002 John Smith 00000001

Anyone could help me to build a SOQL query to accomplish such result?

Leonardo Lira
  • 15
  • 5

2 Answers2

0
SELECT Id, CaseNumber, Contact.Name, Parent.CaseNumber
FROM Case

You need to read up a bit about relationship queries which is Salesforce's way of doing joins between tables.

You can go "up" 5 times, have 5 dots (SELECT Account.Owner.Profile.Name FROM Case). And you can go "down" the related lists too (SELECT Id, (SELECT Email FROM Contacts) FROM Account

https://trailhead.salesforce.com/content/learn/modules/soql-for-admins/create-relationship-queries-with-standard-objects and https://trailhead.salesforce.com/content/learn/modules/apex_database/apex_database_soql may help.

eyescream
  • 18,088
  • 2
  • 34
  • 46
0

Try the following query and let us know how it turns out for you. We have tested this in an org and it works without complication. Have a blessed one.

SELECT Id, CaseNumber, Contact.Name, Parent.CaseNumber FROM Case
coderunner
  • 13
  • 2