Llama Land

EXISTS

A few days ago, I spent about an hour trying to put together a SQL query that would meet specific criteria for a project at work. The trick was that I wanted to return a boolean result from a subquery which had no boolean columns. The solution, is to use the EXISTS function/operator (in PostgreSQL, at least). By passing the query to the EXISTS function, it returns true if at least one row is returned, and false if zero rows are returned. Here's the query:
SELECT count(id) from \${row} where emailed='f' AND cl='\${change}' AND subtest='APPS build' AND current_status='FAILED' AND EXISTS(SELECT id FROM \${row} where cl='\${change}' AND subtest='CUDA build' AND current_status='PASSED' ORDER BY ID DESC LIMIT 1) ;