CODEWITHSUNDEEP
mysql
Carlos H
Carlos H

Reputation: 1

MySQL - How to get one row from a table with multiple columns with true or false

I´m trying to write a query to get in a single row the records with values equals to TRUE in several columns. I thought it will be easy but it´s becoming though.

Having a table like this:

ITEMS   COL1    COL2    COL3    COL4    COL5    COL6
----------------------------------------------------
ITEM1   TRUE    FALSE   FALSE   FALSE   FALSE   FALSE
ITEM1   FALSE   FALSE   TRUE    FALSE   FALSE   FALSE
ITEM1   FALSE   FALSE   FALSE   FALSE   FALSE   FALSE
ITEM1   FALSE   FALSE   FALSE   FALSE   FALSE   TRUE
ITEM2   FALSE   TRUE    FALSE   FALSE   FALSE   FALSE
ITEM2   TRUE    FALSE   FALSE   FALSE   FALSE   FALSE
ITEM2   FALSE   FALSE   FALSE   TRUE    FALSE   FALSE

I need to get something like this:

ITEMS   COL1    COL2    COL3    COL4    COL5    COL6
----------------------------------------------------
ITEM1   TRUE    FALSE   TRUE    FALSE   FALSE   TRUE
ITEM2   TRUE    TRUE    FALSE   TRUE    FALSE   FALSE

I tried using distinct and group with in a single table, something like this:

Select distinct ITEMS, COL1,COL2,COL3,COL4,COL5,COL6
FROM TABLE
WHERE   (COL10=’TRUE’  or COL2=’TRUE’ or COL3=’TRUE’ or COL4=’TRUE’ or COL5=’TRUE’ or COL6=’TRUE’)
GROUP BY ITEMS

But it doesn´t work, also I tried doing joins to the same table but the results were the same, it’s showing FALSE in columns with TRUE.

So, can somebody give a hint about how can I get the desired result? Thanks in advance.

Upvotes: 0

Views: 47

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269863

MySQL doesn't have aggregation functions for booleans. However, these are really 0 (for false) and 1 (for true) in disguise. So, try max():

select items, max(col1), max(col2), max(col3), max(col4), max(col5), max(col6)
from t
group by items;

If these are stored as strings, then -- by coincidence -- the above actually works because 'TRUE' > 'FALSE'. You could be more methodical:

select items,
       (case when max(col1 = 'TRUE') > 0 then 'TRUE' else 'FALSE' end) as col1,
       . . .
from t
group by items;

Upvotes: 1

user5714480
user5714480

Reputation: 

select items, min(col1), min(col2), min(col3), min(col4), min(col5), min(col6)
from t
group by items;

or

Select distinct ITEMS, COL1,COL2,COL3,COL4,COL5,COL6
FROM TABLE
WHERE   (COL10=’1’  or COL2=’1’ or COL3=’1’ or COL4=’1’ or COL5=’1’ or COL6=’1’
GROUP BY ITEMS

Dont use boolean in the Database

Upvotes: 0

Related Questions