SQL getting values by specific needs-CodePudding

so I have a database with many columns for badge holding! I wanted to select every badge where the badge value = 1 but here's the structure:

 -------------------------------- 
|username| badge1| badge2| badge3|
 -------------------------------- 
|bob     | 0     |1      |1      |
 --------------------------------

How can I use "where" to only get 1's without doing each of them separately like: where badge1 = 1, since I have many badges! A way would be appreciated!

CodePudding user response：

One in either of them.

SELECT *
FROM BadgeHoldings
WHERE 1 IN (badge1, badge2, badge3)

CodePudding user response：

Try this

  Select * from table
  Where badge1||badge2|| badge3
  Like '%1%'

CodePudding user response：

Some alternatives just for fun & giggles.

SELECT 'or =1' as method, t.*
FROM BadgeHoldings t
WHERE (badge1=1 OR badge2=1 OR badge3=1);

SELECT 'or' as method, t.*
FROM BadgeHoldings t
WHERE (badge1 OR badge2 OR badge3);

SELECT 'not and =0' as method, t.*
FROM BadgeHoldings t
WHERE NOT (badge1=0 AND badge2=0 AND badge3=0);

SELECT 'not and' as method, t.*
FROM BadgeHoldings t
WHERE NOT (badge1 AND badge2 AND badge3);

SELECT 'greatest' as method, t.*
FROM BadgeHoldings t
WHERE 1 = GREATEST(badge1, badge2, badge3);

SELECT 'plus' as method, t.*
FROM BadgeHoldings t
WHERE (badge1   badge2   badge3);

SELECT 'any 1 union' as method, t.*
FROM BadgeHoldings t
WHERE 1 = ANY (select badge1 union select badge2 union select badge3);

SELECT 'multiply' as method, t.*
FROM BadgeHoldings t
WHERE (badge1*badge2*badge3) = 0;

SELECT 'bitwise-or' as method, t.*
FROM BadgeHoldings t
WHERE (badge1 | badge2 | badge3);

SELECT 'not all 0' as method, t.*
FROM BadgeHoldings t
WHERE NOT(0 = ALL (select badge1 union 
                   select badge2 union
                   select badge3));

SELECT 'concat_ws' as method, t.*
FROM BadgeHoldings t
WHERE CONCAT_WS(' ', ' ', badge1, badge2, badge3, ' ') LIKE '% 1 %';

db<>fiddle here