mysql select only fields with 'Yes' value, not 'No' value-CodePudding

I have a table named crm_users that contains user data that includes some US states that contain Yes and No values. Since each state is its own field, I am having a difficult time having only states with Yes returned by the query; it returns the state fields even with No values.

EDIT: The only fields that should be returned are the id, password,..., (any state that has 'Yes' as the value).

If a state has 'Yes' as its value, it should be returned; if the state has 'No' for its value, that state should not be displayed in the results set.

This is the table:

 CREATE TABLE `crm_users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(48) NOT NULL,
  `password` varchar(255) NOT NULL,
  `uemail` varchar(255) DEFAULT NULL,
  `urole` varchar(5) DEFAULT NULL,
  `ustatus` varchar(8) DEFAULT NULL,
  `availability` varchar(45) DEFAULT NULL,
  `timezone` varchar(4) DEFAULT NULL,
  `localphone` int DEFAULT NULL,
  `faxnumber` int DEFAULT NULL,
  `office_add` varchar(64) DEFAULT NULL,
  `office_city` varchar(36) DEFAULT NULL,
  `office_state` varchar(36) DEFAULT NULL,
  `office_zipcode` int DEFAULT NULL,
  `Alabama` varchar(45) DEFAULT 'No',
  `California` varchar(45) DEFAULT 'No',
  `Colorado` varchar(45) DEFAULT 'No',
  `Connecticut` varchar(45) DEFAULT 'No',
  `Florida` varchar(45) DEFAULT 'No',
  `Georgia` varchar(45) DEFAULT 'No',
  `Maryland` varchar(45) DEFAULT 'No',
  `Michigan` varchar(45) DEFAULT 'No',
  `Minnesota` varchar(45) DEFAULT 'No',
  `Jersey` varchar(45) DEFAULT 'No',
  `Ohio` varchar(45) DEFAULT 'No',
  `Oregon` varchar(45) DEFAULT 'No',
  `Pennsylvania` varchar(45) DEFAULT 'No',
  `Tennessee` varchar(45) DEFAULT 'No',
  `Virginia` varchar(45) DEFAULT 'No',
  `Washington` varchar(45) DEFAULT 'No',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

And here is one record contained in this table (the 'No' and 'Yes' values are for the states columns):

> # id, username, password, uemail, urole, ustatus, availability, timezone, localphone, faxnumber, office_add, office_city, office_state, office_zipcode, Alabama, California, Colorado, Connecticut, Florida, Georgia, Maryland, Michigan, Minnesota, Jersey, Ohio, Oregon, Pennsylvania, Tennessee, Virginia, Washington
'1', 'will', '', '', 'admin', 'active', 'Available', '', '', '', '', '', '', '', 'No', 'Yes', 'Yes', 'No', 'Yes', 'Yes', 'No', 'Yes', 'Yes', 'Yes', 'No', 'Yes', 'No', 'Yes', 'No', 'Yes'

I've tried the following:

SELECT id, username, Alabama, California FROM crm_users
where 'Yes' in (Alabama, California, Colorado, Connecticut, Florida, Georgia, Maryland, Minnesota, Jersey, Ohio, Oregon, Pennsylvania,
Tennessee, Virginia, Washington) and id=1;

But, it returns even the states with 'No' for values.

Any help is always appreciated.

CodePudding user response：

I don't think you can dynamically select columns based on a condition and only display those, but if you want you can avoid 'No' values been printed in the result by using CASE condition.

Like,

SELECT id,
    username,
    CASE WHEN 'Yes' = Alabama THEN Alabama ELSE NULL END AS Alabama,
    CASE WHEN 'Yes' = California THEN California ELSE NULL END AS California,
    CASE WHEN 'Yes' = Colorado THEN Colorado ELSE NULL END AS Colorado,
    CASE WHEN 'Yes' = Connecticut THEN Connecticut ELSE NULL END AS Connecticut,
    CASE WHEN 'Yes' = Florida THEN Florida ELSE NULL END AS Florida,
    CASE WHEN 'Yes' = Georgia THEN Georgia ELSE NULL END AS Georgia,
    CASE WHEN 'Yes' = Maryland THEN Maryland ELSE NULL END AS Maryland,
    CASE WHEN 'Yes' = Minnesota THEN Minnesota ELSE NULL END AS Minnesota,
    CASE WHEN 'Yes' = Jersey THEN Jersey ELSE NULL END AS Jersey,
    CASE WHEN 'Yes' = Ohio THEN Ohio ELSE NULL END AS Ohio,
    CASE WHEN 'Yes' = Oregon THEN Oregon ELSE NULL END AS Oregon,
    CASE WHEN 'Yes' = Pennsylvania THEN Pennsylvania ELSE NULL END AS Pennsylvania,
    CASE WHEN 'Yes' = Tennessee THEN Tennessee ELSE NULL END AS Tennessee,
    CASE WHEN 'Yes' = Virginia THEN Virginia ELSE NULL END AS Virginia,
    CASE WHEN 'Yes' = Washington THEN Washington ELSE NULL END AS Washington
FROM crm_users
WHERE id = 1;

This will give you,

┌────┬──────────┬─────────┬────────────┬──────────┬─────────────┬─────────┬─────────┬──────────┬───────────┬────────┬──────┬────────┬──────────────┬───────────┬──────────┬────────────┐
│ id │ username │ Alabama │ California │ Colorado │ Connecticut │ Florida │ Georgia │ Maryland │ Minnesota │ Jersey │ Ohio │ Oregon │ Pennsylvania │ Tennessee │ Virginia │ Washington │
├────┼──────────┼─────────┼────────────┼──────────┼─────────────┼─────────┼─────────┼──────────┼───────────┼────────┼──────┼────────┼──────────────┼───────────┼──────────┼────────────┤
│  1 │ will     │         │ Yes        │ Yes      │             │ Yes     │ Yes     │          │ Yes       │ Yes    │      │ Yes    │              │ Yes       │          │ Yes        │
└────┴──────────┴─────────┴────────────┴──────────┴─────────────┴─────────┴─────────┴──────────┴───────────┴────────┴──────┴────────┴──────────────┴───────────┴──────────┴────────────┘

CodePudding user response：

I think I get it

create a virtual column like

alter table xxx add column `yes_states` text generated always as (concat(if(Alabama="Yes","Alabama,",""),if(California="Yes","California,","")...) VIRTUAL;

Get all the Yes states from the virtual column, and split it using comma, cuz all the states are followed by a comma

select id, yes_states from xxx where yes_states != '';