Mysql Question posted in
The official MySQL documentation can be found here.

MySQL LEFT JOIN and OR condition with fallback

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?

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0
    • ORDER BY CASE in subquery creates priority.
    • p=1 in outer query filters by unwanted languages
      • language-specific URL if exists
      • else first fallback URL
      • otherwise null
    SET @lang = 'hu';
SELECT  c.companyID, w.lang, w.URL
FROM companies c
LEFT JOIN (
    SELECT w.*, ROW_NUMBER() OVER (
            PARTITION BY w.companyID 
            ORDER BY 
                CASE 
                    WHEN w.lang = @lang THEN 1
                    WHEN w.lang IS NULL THEN 2
                    ELSE 3
                END
        ) AS p
    FROM webpages w
    WHERE w.lang = @lang OR w.lang IS NULL
) w ON c.companyID = w.companyID AND w.p = 1
ORDER BY c.companyID;

    Output:

    companyID lang URL
    1 hu hu.example.com/x
    2 null null
    3 null http://www.example.com/y
    4 null null

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search