CODEWITHSUNDEEP
sql-servert-sql
secondbreakfast
secondbreakfast

Reputation: 4372

TSQL turning these multiple CASE queries into one query

I have the following CASE queries that return 1 if they find anything, and 0 otherwise. I would like to turn these into a single query that returns 1 if any of them are true, or 0 otherwise. How can I acomplish this?

SELECT CASE WHEN count(PLACE.CODE) > 0 THEN 1 ELSE 0 END
FROM PLACE
WHERE STYLE = 'RED'
AND RULES = 'NO'



SELECT CASE WHEN count(GARDEN.AREA) > 0 THEN 1 ELSE 0 END
FROM GARDEN
WHERE PLACE = 'GROUND'
AND MAZE = '1'


SELECT CASE WHEN count(place_area.AVAILABLE_AREA) > 0 THEN 1 ELSE 0 END
FROM PLACE_AREA as place_area
INNER JOIN USED_PLACE as used_place
    ON used_place.COLOR = 'RED'
    AND used_place.MAKE = 'INDUSTRY'
WHERE place_area.CODE = 'FLOOR'
AND place_area.DANCE = '0'

Upvotes: 0

Views: 36

Answers (2)

Devi Prasad
Devi Prasad

Reputation: 41

SELECT 
CASE 
    WHEN 
            EXISTS(
                SELECT TOP 1 1
                FROM PLACE
                WHERE STYLE = 'RED'
                AND RULES = 'NO'
                AND count(PLACE.CODE) > 0
            ) 
            OR EXISTS (
                SELECT TOP 1 1 
                FROM GARDEN
                WHERE PLACE = 'GROUND'
                AND MAZE = '1'
                AND count(GARDEN.AREA) > 0
            )
            OR EXISTS (
                SELECT TOP 1 1
                FROM PLACE_AREA as place_area
                INNER JOIN USED_PLACE as used_place
                    ON used_place.COLOR = 'RED'
                    AND used_place.MAKE = 'INDUSTRY'
                WHERE place_area.CODE = 'FLOOR'
                AND place_area.DANCE = '0'
                AND count(place_area.AVAILABLE_AREA) > 0
                ) 
    THEN 1 
    ELSE 0
END

Upvotes: 1

Reza
Reza

Reputation: 19843

If I understand you correctly, you can union all the result and calculate the sum, if sum > 0 then there was at least one 1

Select Case When Sum(x.col) > 0 THEN 1 ELSE 0 END from
(
SELECT CASE WHEN count(PLACE.CODE) > 0 THEN 1 ELSE 0 END as col
FROM PLACE
WHERE STYLE = 'RED'
AND RULES = 'NO'

Union All


SELECT CASE WHEN count(GARDEN.AREA) > 0 THEN 1 ELSE 0 END as col
FROM GARDEN
WHERE PLACE = 'GROUND'
AND MAZE = '1'

Union All

SELECT CASE WHEN count(place_area.AVAILABLE_AREA) > 0 THEN 1 ELSE 0 END as col
FROM PLACE_AREA as place_area
INNER JOIN USED_PLACE as used_place
    ON used_place.COLOR = 'RED'
    AND used_place.MAKE = 'INDUSTRY'
WHERE place_area.CODE = 'FLOOR'
AND place_area.DANCE = '0'
) x

Upvotes: 1

Related Questions