Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in Salesforce by (11.9k points)

here are two tables in the salesforce schema

                 +--------------+      +-------------+
               |   Case       |      |   User      |
               |--------------|      |-------------|
               | CaseNumber   |      |id           |
               | ContactId    |      |OwnerId      |
               | IsClosed     |      |UserName     |
               +--------------+      +-------------+

ContactId is a foreign key that maps to OwnerId (notice how foo is in both tables?)

  +-------------------------------------+        +----------------------------+
  |                     Case            |        |             User           |
  |-------------------------------------|        |----------------------------|
  |CaseNumber |  ContactId |  IsClosed  |        | id | OwnerId | UserName    |
  |                                     |        |                            |
  |1             foo          false     |        | 42   foo       bob         |
  |2             bar          true      |        | 99   bar       joe         |
  |3             foobar       false     |        | 10   foobar    sally       |
  |                                     |        |                            |
  +-------------------------------------+        +----------------------------+

I want to show the relationship between a Case and a User. (eg. bob has case 1, joe has case 2 ect..) I believe that is called a Left Inner Join. Correct me if I am wrong.

+-------------------------------------+
|                                     |
|  Case.CaseNumber | User.UserName    |
|                                     |
|     1               bob             |
|     2               joe             |
|     3               sally           |
|                                     |
+-------------------------------------+

Here is how I think the SOQL query should look.

SELECT Case.CaseNumber, User.OwnerId

FROM Case

WHERE Case.ContactId IN (SELECT User.OwnerId FROM User)

I'm am following the examples in the Salesfoce SOQL documentationhttp://wiki.developerforce.com/page/A_Deeper_look_at_SOQL_and_Relationship_Queries_on_Force.com

What is the correct syntax to query CaseNumber and UserName ?

1 Answer

0 votes
by (32.1k points)

This SOQL should give you what you need:

SELECT CaseNumber, Owner.Name FROM Case

Here's a more general link that should help in writing queries like this

http://www.salesforce.com/us/developer/docs/soql_sosl/

Related questions

0 votes
1 answer
asked Jul 17, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
asked Jul 25, 2019 in Salesforce by Kartik12234 (11.9k points)

Browse Categories

...