CODEWITHSUNDEEP
mysql
user2401547
user2401547

Reputation: 131

Operand should contain only 1 column Mysql Query

I am using the following query to fetch multiple column values based on the condition specified in the case statement and encountered the following error operand should contain only 1 column mysql query.I just can't figure out what the problem is.Is there a simple fix to this, or another way to write my query?

SELECT id,
          CASE 
             WHEN id='' THEN (select * where name='abc')
             ELSE (select * from sample)
          END
    FROM sample WHERE name='abc' and status='xyz'

Upvotes: 0

Views: 44

Answers (1)

fthiella
fthiella

Reputation: 49049

You could use a union query that combines three different queries:

select * from samples
where name='abc' and status='xyz'

union all

select * from samples
where
  name='abc' and not exists (
    select * from samples
    where name='abc' and status='xyz'
  )

union all

select * from samples
where
  not exists (
    select * from samples
    where name='abc'
  )

  • the first part will return all records from samples that match the name and status condition

  • the second part will return all records from sample that match the name condition, but only if the first query doesn't return anything

  • the thirds part will return all records from sample whenever both the first and second query don't return anything

(those three queries can be combined together with ORs but the union query is easier to read and clearer)

Upvotes: 1

Related Questions