HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

How to check if WHERE = value does not exist and make it zero by default?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
wheremakeexistvaluedefaultdoeshowandchecknot

Problem

My goal is to check if lineId's value exists. If not, it has to be zero by default.
Here is my datas:

For example:

SELECT lineId from table_name WHERE lineId = 80

There is no lineId = 80, then I want to see that lineId = 0 records.

Thanks, sincerely :)

Solution

Another method:

SELECT lineId 
FROM table_name 
WHERE lineId = 80

UNION ALL

SELECT lineId 
FROM table_name 
WHERE lineId = 0
  AND NOT EXISTS
      ( SELECT 1 
        FROM table_name 
        WHERE lineId = 80
      ) ;


And similar with a CTE:

WITH cte AS
  ( SELECT lineId 
    FROM table_name 
    WHERE lineId = 80
  )

SELECT * 
FROM cte

UNION ALL

SELECT lineId 
FROM table_name 
WHERE lineId = 0
  AND NOT EXISTS
      ( SELECT *
        FROM cte
      ) ;

Code Snippets

SELECT lineId 
FROM table_name 
WHERE lineId = 80

UNION ALL

SELECT lineId 
FROM table_name 
WHERE lineId = 0
  AND NOT EXISTS
      ( SELECT 1 
        FROM table_name 
        WHERE lineId = 80
      ) ;
WITH cte AS
  ( SELECT lineId 
    FROM table_name 
    WHERE lineId = 80
  )

SELECT * 
FROM cte

UNION ALL

SELECT lineId 
FROM table_name 
WHERE lineId = 0
  AND NOT EXISTS
      ( SELECT *
        FROM cte
      ) ;

Context

StackExchange Database Administrators Q#316505, answer score: 6

Revisions (0)

No revisions yet.