CODEWITHSUNDEEP
mysql
srakl
srakl

Reputation: 2619

Multiple column search for same value

I'm trying to search multiple columns matching the value of 1

columns: 1S, 2S, 3S, 4S

The database looks like this

aa, 0, 0, 1, 0
ab, 1, 0, 1, 0
ac, 1, 0, 0, 0
ad, 1, 1, 1, 0

i would like to query to return 1 row, that only shows if a column with the value of 1 exists. Example it should return

1, 1, 1, 0

Columns 1S, 2S and 3S are 1 because there is a row with the value of 1. 4S is 0 because there is NO row with the value of 1.

i tried this, but i'm getting an mysql error #1242 - Subquery returns more than 1 row

SELECT * FROM `show` AS s 
JOIN (`make` AS v, zip AS p)
ON (s.make = v.id AND s.zip = p.zip)
WHERE (
SELECT CONCAT_WS(' AND ', CONCAT(column_name, ' = 1 '))
                            FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'show'
                             AND table_schema = 'DB_NAME'
                             AND column_name NOT IN ('id')

                           )

Upvotes: 1

Views: 253

Answers (1)

Maxim Zhukov
Maxim Zhukov

Reputation: 10140

Ok, what about this:

SELECT 
  IF(SUM(1S) > 0, 1, 0), 
  IF(SUM(2S) > 0, 1, 0), 
  IF(SUM(3S) > 0, 1, 0), 
  IF(SUM(4S) > 0, 1, 0)
FROM `show`

Upvotes: 2

Related Questions