Combine two different List into final value or achieve same by single query

Question

My First query is returning like this

select id,ProgramType__c,Program__c, Account.ProviderType__c,Account.Sector__C from Case where Account.ProviderType__c ='Public'


Id                 ProgramType__c  Program__c       Account.ProviderType__c  Account.Sector__C
---                 -------------  -----------      --------------           -------
5009D000002fEHzQAM  RCC             MFP               Public                   Urban
5009D000002fEHzBAW  CPA             MPC               Public                   Rural

I will take out the id from first query(case object) and will query contact object as

select Referral__c,Name,Role__C from contact where Referral__c in (select Id from case)

Referral__c         Name       Role__C            
------------        ----       -------           
5009D000002fEHzQAM  James      Applicant     
5009D000002fEHzQAM  Antony     Co Applicant  
5009D000002fEHzQAM  Williams   Co Applicant   
5009D000002fEHzBAW  Mark       Applicant
5009D000002fEHzBAW  Spencer    Co Applicant

Finally i Want output like this

ID                  Name        Role           ProviderType__c  Sector__C  ProgramType__c     Program__c
------------        -----       -----          ----------       -------   --------------     -----------

5009D000002fEHzQAM  James      Applicant       Public           Urban     RCC MFP
5009D000002fEHzQAM  Antony     Co Applicant    Public           Urban     RCC MFP
5009D000002fEHzQAM  Williams   Co Applicant    Public           Urban     RCC MFP
5009D000002fEHzBAW  Mark       Applicant       Public           Rural     CPA MPC
5009D000002fEHzBAW  Spencer    Co Applicant    Public           Rural     CPA MPC

I know we have to query like this ,as account as parent object and others as child

 select id,(select id from cases),(select id from contacts) from account

But I am doing other way around as case as my parent and contact as child as they designed object like that

I tried all my best to achieve this by single query and by two queries in apex by putting first into Map and second into list but I am not able to achieve.

Can you please help on this?

Answer 1

Would the following query be what you are looking for?

SELECT 
    con.Name, con.Referral__c, con.Role__c,
    case.Id, case.ProgramType__c, case.Program__c, 
    acc.ProviderType__c, acc.Sector__c
FROM Contact con, con.Referral__r case, case.Account acc 
WHERE acc.ProviderType__c = 'Public'

Answer 2

The columns that you wanted to add to the final output are from Case (parent) and Account (grandparent) and you already have the Referral__c lookup field to Case. So you should be able to reference them fields using dot notation:

SELECT Name, Role__c, Referral__r.Account.ProviderType__c, Referral__r.Account.Sector__c, Referral__r.ProgramType__c, Referral__r.Program__c
FROM contact

OR if your Contact's account is the same as the Case's account. Then simply get the Account field from the parent level:

SELECT Name, Role__c, Account.ProviderType__c, Account.Sector__c, Referral__r.ProgramType__c, Referral__r.Program__c
FROM contact