PHP SQL declare YES or NO based on count withing query-CodePudding

I am searching for a way to make the sql statement declare a AS example as Yes when the value is greater than 0 and No if 0 after I count the result while retreiving the data from the database.

This (is not working) is what I tried so far. I hope my tryout is making sence. I first make a select count on planning_clientid the result is 0 or more. If the result is 0 I need timesonplanning (perhaps change that name after its working) to be No and if higher than Yes. So in steady of the count result I need Yes or No, Is this possible?

Part that is not working:

(CASE WHEN(SELECT COUNT(planning_clientid) FROM tbl_planning WHERE planning_afgehandeld = 0 AND a.client_id = planning_clientid)> 0 THEN 'Yes' ELSE 'No' END AS timesonplanning

The whole statement:

   SELECT a.*, own1.naam AS resposible_btw, own2.naam AS resposible_client, (CASE WHEN(SELECT COUNT(planning_clientid) FROM tbl_planning WHERE planning_afgehandeld = 0 AND a.client_id = planning_clientid)> 0 THEN 'Yes' ELSE 'No' END AS timesonplanning FROM tbl_clients a LEFT JOIN gebruikers own1 ON own1.id = a.client_ownerob LEFT JOIN gebruikers own2 ON own2.id = a.client_owner

Desired result:

timesonplanning Yes (when count if higher than 0) timesonplanning No (when count is 0)

CodePudding user response：

The error you indicated in the comments indicates a syntax error (albeit in Dutch). This is indeed to be expected as the query you included doesn’t close the initial parenthesis character ( you used right before your CASE keyword.

Resolve this by removing the initial parenthesis before CASE (as it doesn’t seem particularly necessary in this context), or properly close it by inserting a close parenthesis ) after the END keyword:

SELECT a.*, own1.naam AS resposible_btw, own2.naam AS resposible_client, (CASE WHEN(SELECT COUNT(planning_clientid) FROM tbl_planning WHERE planning_afgehandeld = 0 AND a.client_id = planning_clientid)> 0 THEN 'Yes' ELSE 'No' END) AS timesonplanning FROM tbl_clients a LEFT JOIN gebruikers own1 ON own1.id = a.client_ownerob LEFT JOIN gebruikers own2 ON own2.id = a.client_owner