Fetch rows either with one matching condition or all rows if no matching rows

Question

I have a simple table

**targeted_url_redirect   targeted_countries        msg_type    non_targeted_url**
http://new.botweet.com    india,nepal,philippines   NEW         http://twisend.com
http://expapers.com       United States,Canada      OLD         http://all.twisend.com
https://tweeasy.com       india,england             OLD         http://all.twisend.com

I receive traffics on my website followerise.com and I want to redirect users to specific urls based on their countries as defined in the above table. I am able to write query to get rows for the users who coming from the countries that are target stored in my database. But I am looking for a solution to get all the rows if the targeted_countries condition not return any rows.

I written below queries.

SELECT * FROM tweeasy_website_redirection
WHERE message_type='OLD'
  AND targeted_countries LIKE '%@country%'

This query gives me desired rows if visitor coming from the countries india,england,United States,Canada

But I want all rows (2nd and 3rd) should be fetched if a user coming from the countries not specified in targeted_countries column.

Also let me know if I need to restructure this table into 2 or more tables to get desired result.

You should revise the schema and don't store comma delimited lists. See ["Is storing a delimited list in a database column really that bad? "](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad?r=SearchResults&s=1|190.3718). — sticky bit, Mar 18 '20 at 21:41

GMB · Accepted Answer · 2020-03-18T21:41:28.607

One option uses not exists:

SELECT t.* 
FROM tweeasy_website_redirection t
WHERE 
    t.message_type = 'OLD' 
    AND (
        t.targeted_countries LIKE '%@country%'
        OR NOT EXISTS (
            SELECT 1 
            FROM tweeasy_website_redirection t1
            WHERE t1.targeted_countries LIKE '%@country%'
        )
    )

When it comes to the structure of your table: one design flaw is to store list of values as CSV list. You should have two separate tables: one to store the urls, and the other to store the 1-N relationship between urls and countries. Something like:

table "url"
id    targeted_url_redirect     msg_type    non_targeted_url
 1    http://new.botweet.com    NEW         http://twisend.com
 2    http://expapers.com       OLD         http://all.twisend.com
 3    https://tweeasy.com       OLD         http://all.twisend.com

table "url_countries"
url_id    country
1         india
1         nepal
1         philippines
2         united states
2         canada
3         india
3         england

@prograshid: `EXISTS` just checks if some row exist that satisfy the condition; we don't really need the results, we can discard them with `SELECT 1`. That's just a coding style, `SELECT *` would be equivalent, as for `SELECT NUL` or `SELECT 'foo'`. — GMB, Mar 18 '20 at 21:43

score 0 · Answer 2 · answered Mar 19 '20 at 02:42

0

select * from tweeasy_website_redirection where targeted_countries not in (SELECT targeted_countries FROM stack WHERE targeted_countries LIKE '%@country%')

answered Mar 19 '20 at 02:42

Eaimie

45
9

Fetch rows either with one matching condition or all rows if no matching rows

2 Answers2