CODEWITHSUNDEEP
sqlite
Ben
Ben

Reputation: 163

sqlite if no rows returned print 0

I have the following query:

 SELECT user_tag, device_type,  ip_address

 FROM
 devices 

 WHERE (user_tag like '|%frt%|'
 OR user_tag like '|%bck%|'
 OR user_tag like '|%ppp%|'
 OR user_tag like '|%smes%|'
 OR user_tag like '|%gm%|')

Right now if a record is only returned for the user_tag 'frt' I only receive one row (as you would expect). Is there a way to set a default value for the blank rows, so for example I would receive a value of '0' or something similar for the rows that don't return any real data?

Upvotes: 0

Views: 3352

Answers (2)

Scen
Scen

Reputation: 1720

This query is a 'rough draft'. Since I work with all types of SQL, I have trouble with SQLite-specific syntax sometimes, and I don't have any way to test this query right now, so it may need some syntax modification.

But it gets the idea cross... Using your list of hard-coded values, make it a subquery and do a left join on it instead of a where.

user_tag_exists should be 0 when there aren't any user tags for the type given.

Select CASE WHEN d.user_tag is null THEN '0' ELSE '1' END AS user_tag_exists,
       tagTypes.tagType,
       d.user_tag, d.device_type, d.ip_address
From
(
    Select '%frt%' as tagType
    UNION
    Select '%bck%' as tagType
    UNION
    Select '%ppp%' as tagType
    UNION
    Select '%smes%' as tagType
    UNION
    Select '%gm%' as tagType
) as tagTypes
left join
    devices d
        on d.device_type like tagTypes.tagType

Upvotes: 1

Scen
Scen

Reputation: 1720

I'm not sure if you want:

  1. A value of '0' in a column instead of null
  2. A row with '0's if no user_tag exists that are like a value.

If you are looking for 1), you can use a CASE statement.

 SELECT 
        user_tag, 
        CASE WHEN device_type is not null THEN device_type ELSE '0' END as device_type, 
        ip_address

 FROM
 devices 

 WHERE (user_tag like '|%frt%|'
 OR user_tag like '|%bck%|'
 OR user_tag like '|%ppp%|'
 OR user_tag like '|%smes%|'
 OR user_tag like '|%gm%|')

This would give you a device_type of '0' even if device_type was null.

Upvotes: 0

Related Questions