EN VI

Finding the names of columns with a common value in MySQL?

How to Finding the names of columns with a common value in MySQL

I have a table named 'fiction' created in MySQL. The table looks like this

fiction:

id	name	gender	image	browser	os
1	chris	male	1	0	ios
2	emma	female	1	1	ios
3	james	male	0	0	android
4	lucas	male	1	0	android
5	amelia	female	0	1	ios
6	olivia	female	1	0	ios

What I want to do is to retrieve the names of the columns where the records in the 'gender' column have the value 'male', and all other columns have the same value. For example, If I run this code:

SELECT * FROM fiction WHERE gender = "male";

The output will be:

id	name	gender	image	os
1	chris	male	1	ios
3	james	male	0	android
4	lucas	male	1	android

As you can see, the 'browser' column has a common value of '0' in all records. So I want this column name, 'browser'. Another example:

SELECT * FROM fiction WHERE gender = "female";

Output:

id	name	gender	image	browser	os
2	emma	female	1	1	ios
5	amelia	female	0	1	ios
6	olivia	female	1	0	ios

As you can see here, the 'os' column has a common value in all of them. I need an SQL statement that will show me these common columns.

I used GROUP BY and HAVING expressions, but as you can guess, they didn't solve my problem. I think I need to do something related to the information_schema table.

Solution:

SELECT *
FROM (
   SELECT 'name' col, name, count(*) c FROM mytable WHERE gender = 'male' group by name union all
   SELECT 'gender', gender, count(*) c FROM mytable WHERE gender = 'male' group by gender union all
   SELECT 'image', image, count(*) c FROM mytable WHERE gender = 'male' group by image union all
   SELECT 'browser', browser, count(*) c FROM mytable WHERE gender = 'male' group by browser union all
   SELECT 'os', os, count(*) c FROM mytable WHERE gender = 'male' group by os 
   
) x
WHERE x.c = (SELECT count(*) FROM mytable WHERE gender = 'male');
;

Solution for female is the same, just replace 'mail' by 'female'.

see: DBFIDDLE