MySQL LEFT JOIN and OR condition with fallback

Question

I have many tables, but the only one interesting here is the following, let's call it webpages

| companyID | lang | URL
| 1         | hu   | hu.example.com/x
| 1         | en   | en.example.com/x
| 1         | NULL | www.example.com/x
| 1         | NULL | www.example.com/x2
| 3         | NULL | www.example.com/y
| 4         | en   | en.example.com/z

My SQL is something like this

SELECT `companies`.*, `webpages`.`lang`, `webpages`.`URL`
FROM `companies` LEFT JOIN `webpages`
ON `companies`.`companyID` = `webpages`.`companyID`
AND (`webpages`.`lang` = :lang OR `webpages`.`lang` IS NULL)

For example for lang = 'hu' I got this:

| companyID | lang | URL
| 1         | hu   | hu.example.com/x
| 1         | NULL | www.example.com/x
| 1         | NULL | www.example.com/x2
| 2         | NULL | NULL
| 3         | NULL | www.example.com/y
| 4         | NULL | NULL

I'd like to achieve the following for lang = 'hu':

| companyID | lang | URL
| 1         | hu   | hu.example.com/x
| 2         | NULL | NULL
| 3         | NULL | www.example.com/y
| 4         | NULL | NULL

And for lang = 'de':

| companyID | lang | URL
| 1         | NULL | www.example.com/x2
| 2         | NULL | NULL
| 3         | NULL | www.example.com/y
| 4         | NULL | NULL

And for lang = 'en':

| companyID | lang | URL
| 1         | en   | en.example.com/x
| 2         | NULL | NULL
| 3         | NULL | www.example.com/y
| 4         | en   | en.example.com/z

So lang = NULL is a fallback for the language specific URL and a single company can have multiple fallback URLs. If there is no language specific URL given, then either a fallback URL should be chosen or if there is no fallback URL then the value should be NULL.

I am not sure if this is possible with a single SQL, currently I use PHP code to achieve the same thing, but it would be fun to have an SQL for this. Any advice how to do this?

Answer 1

You want to select the line with the language code (when found), and otherwise the line with language code is null?

SELECT 
  `companies`.`companyID`, 
  MIN(CASE WHEN w1.`lang` IS NOT NULL THEN w1.`lang`
                                  ELSE w2.`lang` END) as lang,
  MIN(CASE WHEN w1.`URL` IS NOT NULL THEN w1.`URL`
                                 ELSE w2.`URL` END) as URL
FROM `companies` 
LEFT JOIN `webpages` w1 ON `companies`.`companyID` = w1.`companyID`
      AND (w1.`lang` = 'hu' )
LEFT JOIN `webpages` w2 ON `companies`.`companyID` = w2.`companyID`
      AND (w2.`lang` IS NULL )
GROUP BY companies.companyID

The MIN() is needed to make clear which value to return for a company, when multiple values are found. I choose MIN() to get the lowest (or first) value, I also could have chosen MAX() to get the highest (or last) value.

see: DBFIDDLE