Is it possible to do this in MySQL?

I wonder if this can be done in MySQL.

There are two tables:
T1 has two columns - people and countries (T1.name, T1.country).
T2 has two columns - countries and URLs (T2.country, T2.url).

There is one special record in Table 2 - the "default URL", where the country is NULL.

Given a person's name, I want to find the URL for that person's country. However, if the country does not exist in table 2, I want to return the default URL.

So basically the query is

SELECT T2.url FROM T1, T2 WHERE T1.country = T2.country and T1.name = 'John';

Code (markup):

but it doesn't take into account the case where the country does not exist in table.

Any ideas?

 

You can add a null condition , or have a query with join.

 

Thanks - but if I add a NULL condition, it will return both records in case the country does exist in T2:

 

You can probably do a Left Join from table t1 to table t2

Then to cater for the possibility of a NULL result for country use the IFNULL() control function to replace it with the value you want.

You will likely need to embed a select statement within the function to retrieve the default.

Hope this helps

 

Use case statement:

SELECT T2.url FROM T1, T2 WHERE
T1.name = 'John' AND
CASE
WHEN T1.country = T2.country THEN T2.url
ELSE Default
END CASE
;

 

Thank you!