SQL use EXISTS to display Yes

[Tanager]

I am seeking assistance for the correct syntax to test that a record exists in a child table (table name is Evaluation) and display a "Yes" in a calculated field (Link Test calculated field in image) on a dp for the parent table. I have very little SQL knowledge.

Current SQL syntax that works but is probably incorrect:

SELECT 'Yes' FROM Evaluation
WHERE EXISTS (SELECT ManagingArea FROM Evaluation WHERE ManagingArea = '[@authfield:Evaluators_ManagingArea]' and NTAID = [@field:NTABase_NTAID])

Use case: 800 proposals, 30+ reviewers, I am creating a dashboard that indicates to a reviewer if they have or have not reviewed (child table) a proposal (parent table).  Unique identifies are: [@field:NTAID] in the parent table, [@field:NTAID] and [@Managing Area] in the child table. A view with the parent and child does not work as either inner join (all 800 proposals need to display) or left join because duplicates occur as soon as a proposal has more than 1 review,  which is appropriate and expected. Solution is a dp of the parent table only and use a calculated field to verify if a review exists in the child table. 

I searched high and low on the web and the Caspio forums for similar syntax without luck.   Suggestions for a more appropriate approach are welcomed!

thank you Forum folks for your time!

 

[MayMusic]

In the report that you have based on your parent table you can have:
 

CASE 
WHEN (SELECT COUNT (NTAID) FROM Evaluation WHERE NTAID = [@field:NTABase_NTAID] ) = 0 
THEN NULL
ELSE 'Yes'
END

 

[Tanager]

Thank you @MayMusic!

I really appreciate your expertise and time.  Helps me to learn what is possible to do in Caspio.

[MayMusic]

It is a pleasure