Using EXISTS function to check if the specific table rows exist

Use case

You have two tables:

• T1 with employees and phone numbers
• T2 with employees and their certification statuses

You need to check if there are employees who failed the Certification 2. If yes, get their phone numbers and call them. If no, show a message that everything is okay.

Solution

1. Switch the page to the edit mode.
2. Insert the Table Transformer macro and paste the tables or the macros outputting tables within the macro body.
3. Select the macro and click Edit.
4. In the Presets tab select Custom transformation and click Next.

5. Enter the following SQL query:

   SELECT EXISTS(SELECT * FROM T2 WHERE 'Certification 2' = "FAILED");
   SELECT T1.'Employee', T1.'Job Title', T1.'Phone Number', T2.'Certification 2'
   FROM T1 JOIN T2 ON T1.'Employee'=T2.'Employee'
   WHERE T2.'Certification 2' = "FAILED"

   Copy

   SQL
6. Go to the Messages tab.
7. Enable the No results message customization and type in your text.
   For this example it will be "All employees have successfully passed the Certification 2!"
8. Save the macro and the page.

The behavior when there are employees who failed the Certification 2:

The behavior when there are no any employees who failed the Certification 2: