I am trying to return multiple columns only if a condition matches, else, return something else. Using Sqlite3, my understanding is that the way to do this is using CASE, so something like this:
SELECT CASE WHEN EXISTS(SELECT 1 FROM disk_encryption WHERE user_uuid IS NOT "" AND vault_status = 'on' LIMIT 1) then "Passing" else "Failing" END AS Status, 'Encryption Enabled' AS Policy;
This all works great, it returns the following:
Status | Policy ------------------------------ Passing | Encryption Enabled
Now, I am trying to return contextual data if
Status = 'Failing'.
For example, if
Status = 'Failing', I would like to return the
Status columns, as well as the output of:
SELECT name, type, vault_status FROM disk_encryption;
How would I do this?
Here is simplified test data I am working with:
CREATE TABLE disk_encryption(`name` TEXT, `type` TEXT, `user_uuid` TEXT, `vault_status` TEXT, PRIMARY KEY (`name`)) WITHOUT ROWID; INSERT INTO disk_encryption VALUES ('/dev/disk1s1', 'APFS Encryption', '504', 'on');