SQL where clause selecting specific data-CodePudding

In Microsoft SQL, I am trying to select a specific result from a table when available. Otherwise, other data should be retrieved.

Giving the scenario tableA contains various fruits. I want to collect apples that are red otherwise, any other colors except for red should be retrieved.

I am not sure which approach to go with, having a Case Statement or using EXISTS in the where clause. I tried both method but without the desired outcome.

select * 
from tableA 
where fruit = 'apple' 
and color = case when color = 'red' then 
                      'red' 
                 else color <> 'red' 
            end

Based on some of the initial comments, elaborating on the existing question,

if putting this into If else logic, it would be

If red apples are available, retrieve red apples else retrieve any other colors except for red.

CodePudding user response：

It's unclear what except for EA means.

My interpreation is that you want all rows where fruit = 'apple' and color = 'red', but that if there are no such rows, return all the apples (as none of them are Red)?

I'd use an IF condition to avoid reading the table unnecessarily.

SELECT *
  INTO #temp
  FROM your_table
 WHERE fruit = 'apple' AND color = 'red'

IF @@rowcount = 0
  SELECT *
    FROM your_table
   WHERE fruit = 'apple'
ELSE
  SELECT *
    FROM #temp

DROP TABLE #temp

Demos : https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=b9ce49d1934c149f787c7317f3186656