In the screenshot, Id is the primary key. There could be many of the same Participant__c values in this result set, each with a different Id.
What I’m trying to do is write a query that will return unique Participant__c values IF that particular Participant__c has records in this result set where the Survey_Index_Number__c values are 2001, 2003, and 2005, and their corresponding Completion_Status__c values are Complete. If Participant__c values MATCH that criteria, return that Participant__c value in a result set.
I’m just having a hard time figuring out the best way to approach this, and I’m leaning towards leveraging partitioning logic to do this, but I’m unsure how to start that.
EDIT: My resolution here. This query stores the current count of completed surveys by ContactId:
SELECT
Participant__c as 'ContactId',
COUNT(DISTINCT Survey_Index_Number__c) AS 'SurveyCountComplete'
FROM
(SELECT
Participant__c,
Survey_Index_Number__c,
Completion_Status__c
FROM
Contact_Salesforce c
INNER JOIN
Survey_Result__c_Salesforce sr on sr.Participant__c = c.Id
WHERE
sr.Completion_Status__c = 'Complete'
AND Survey_Index_Number__c IN ('2001', '2003', '2005')) as CompletedSurveys
GROUP BY
Participant__c
7
Assumed that you want unique Participant__c values if the table/result set has at least one row for each Survey_Index_Number__c values: 2001, 20023, 2005 with Completion_Status__c=’Complete’:
select Participant__c
from mytbl a
where Survey_Index_Number__c in ('2001','2003','2005')
and Completion_Status__c='Complete'
group by Participant__c
having count(distinct Survey_Index_Number__c )=3