Is a where clause possible in show columns command?

[Charles Ndethi]

Hi,

I want to return specific column titles based on a certain condition from the column's values:
E.g

in the table Employees:

EmployeeID  Monday   Tuesday   Wednesday
    1          1         0       0
    2          0         1       1
 

The type of the days is BOOLEAN -- one represents present,
zero absent,
I want using the show columns to return the days that employeeID 2 is absent, so something to this effect.

show columns from Employees where EmployeeID = 2 
 
&& Monday = 0 && Tuesday =0 && Wednesday = 0
 
 

Above does not work,
but what would using show columns or not?

and in the query how can use a wildcard instead of having to repeat Monday , Tuesday, Wednesday?


Any help on the logic of this query would be appreciated.
Thanks.

[Rabbit]

Aside from the poor table design, I have no idea what results you're looking for. It sounds like you need to unpivot your data before you do your query. But that's a guess since I don't know the results you're expecting.

[JKing]

With the data you provided your query would never return anything as there is no row where all columns equal zero.

As rabbit has said this is poor table design. You should separate your data. Make one table for employees and their information like name, address etc.
Make another table for attendance. This table could use the date + employeeID as the primary key with an additional column for present/absent.

[dgreenhouse]

SELECT employeeid,
CONCAT((CASE WHEN monday = 0 THEN 'Monday, ' ELSE '' END),
(CASE WHEN tuesday = 0 THEN 'Tuesday, ' ELSE '' END),
(CASE WHEN wednesday = 0 THEN 'Wednesday, ' ELSE '' END),
(CASE WHEN thursday = 0 THEN 'Thursday, ' ELSE '' END),
(CASE WHEN friday = 0 THEN 'Friday, ' ELSE '' END)) AS days_absent
FROM employees WHERE monday=0 OR tuesday=0 OR wednesday=0 OR thursday=0 OR friday=0
 

I'll leave it up to you to figure out how to strip off the trailing comma.