SOQL join or relation query

Question

I have 2 tables Deal__c and DealProduct__c. They have one to many relation. I want to combine 2 SOQL queries with different where clause:

Select Name, Date_Of_Deal__c, Total_Amount__c from Deal__c where Column1__c = 'something'

for deals above I want to get information about corresponding Deal Products but with a where clause (that differs from where clause above):

Select Product_Name__c, Quantity__c from DealProduct__c where ColumnA__c = 'xyz'

Is there a way to get data from both table using 1 query?

Answer 1

Relationship queries can follow child to parent references (see Renato's answer) or parent to child references:

Select Name, Date_Of_Deal__c, Total_Amount__c,
        (select Product_Name__c, Quantity__c from DealProducts__r where ColumnA__c = 'xyz')
from Deal__c
where Column1__c = 'something'

assuming DealProducts__r is the parent to child relationship name (the opposite direction relationship to Deal__r in Renato's answer).

In this case a where clause can be included (and order by etc) in the child query.

This does pull the data in one query and the references (the __r values) can be used to get from one object to the other.

Answer 2

Not directly. In a single SOQL query you can only get the fields of the table you are querying. You can, however, get related fields in the child object.

Assuming your DealProduct__c has a reference to Deal__c, you can get Deal's fields using __r fields.

You would do something like:

SELECT Id, Deal__r.Name, Value__c FROM DealProduct__c WHERE Value__c > 500

In this example, querying for Deal__r.Name will have the same effect of querying the Deal table and obtaining the Deal's Name. This won't bring deals that don't have products that have a value below or equal to 500, though, so you might need to tweak your query condition.