MySQL: select default row (not default values) if other row not available

Question

edit:

I've included the create statements and a small set of test data for you to try out. Therefor, I've changed the example id to 2 in stead of 5 to represent an existing id in the test data.

/ edit

I have three MySQL tables for keeping localized page info:

CREATE TABLE `locale` (
  `languageCode` char(3) NOT NULL,
  `regionCode` char(3) NOT NULL default 'ZZ',
  `isActive` enum('yes','no') NOT NULL default 'no',
  PRIMARY KEY  (`languageCode`,`regionCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `page` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `parentId` int(10) unsigned default NULL,
  PRIMARY KEY  (`id`),
  KEY `FK_page_page_1` (`parentId`),
  CONSTRAINT `FK_page_page_1` FOREIGN KEY (`parentId`) REFERENCES `page` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `pageInfo` (
  `pageId` int(10) unsigned NOT NULL,
  `languageCode` char(3) NOT NULL,
  `regionCode` char(3) NOT NULL default 'ZZ',
  PRIMARY KEY  (`pageId`,`languageCode`,`regionCode`),
  KEY `FK_pageInfo_locale_1` (`languageCode`,`regionCode`),
  KEY `FK_pageInfo_page_1` (`pageId`),
  CONSTRAINT `FK_pageInfo_locale_1` FOREIGN KEY (`languageCode`, `regionCode`) REFERENCES `locale` (`languageCode`, `regionCode`) ON DELETE CASCADE,
  CONSTRAINT `FK_pageInfo_page_1` FOREIGN KEY (`pageId`) REFERENCES `page` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And here is some test data:

/* locale */
INSERT INTO `locale` (languageCode,regionCode,isActive) VALUES ('de','ZZ','yes');
INSERT INTO `locale` (languageCode,regionCode,isActive) VALUES ('en','ZZ','yes');
INSERT INTO `locale` (languageCode,regionCode,isActive) VALUES ('nl','ZZ','yes');
/* page */
INSERT INTO `page` (id,parentId) VALUES (1,NULL);
INSERT INTO `page` (id,parentId) VALUES (2,1);
/* pageInfo */
INSERT INTO `pageInfo` (pageId,languageCode,regionCode) VALUES (1,'de','ZZ');
INSERT INTO `pageInfo` (pageId,languageCode,regionCode) VALUES (1,'en','ZZ');
INSERT INTO `pageInfo` (pageId,languageCode,regionCode) VALUES (1,'nl','ZZ');
INSERT INTO `pageInfo` (pageId,languageCode,regionCode) VALUES (2,'en','ZZ');
INSERT INTO `pageInfo` (pageId,languageCode,regionCode) VALUES (2,'nl','ZZ');

The dilemma:
To retrieve pages with id 2 of all active locales I issue the following SQL statement:

SELECT 
        p.*,   pi.languageCode,   pi.regionCode
    FROM
        page AS p
        INNER JOIN pageInfo AS pi
            ON pi.pageId = p.id
        INNER JOIN locale AS l
            ON l.languageCode = pi.languageCode AND l.regionCode = pi.regionCode
    WHERE
        (p.id = '2')
        AND (l.isActive = 'yes')

How would I alter this statement, so that, when id 2 is not available for a particular locale, it automatically falls back on the root page for that locale (that is: where page.parentId IS NULL)? My goal is to have MySQL give me either one, but not both, for the active locales.

I tried:

WHERE
    (p.id = '2' OR (p.parentId IS NULL))

But, of course, that gives me two records for locales that actually have id 2 also. I'm pretty sure it's possible (either with UNION, sub selects, or a duplicate join on page) but I'm having a total SQL writers block here. I'd appreciate your help.

Answer 1

How about XOR? "one or the other but not both":

WHERE
    (p.id = '5' XOR (p.parentId IS NULL))
                ^---here

Answer 2

Use LEFT JOINs and join both tables twice. The second time you join them specifically for the case of p.parentId IS NULL.

In the SELECT list use IFNULLs. First arguments are for the id specified, second ones are the fallbacks, i.e. the values for the root page.

SELECT
    p.*,
    IFNULL(l.languageCode, lr.LanguageCode) AS languageCode,
    IFNULL(l.regionCode, lr.regionCode) AS regionCode
FROM
    page AS p
    LEFT JOIN pageInfo AS pi
        ON pi.pageId = p.id
    LEFT JOIN locale AS l
        ON l.languageCode = pi.languageCode AND l.regionCode = pi.regionCode
    LEFT JOIN pageInfo AS pir
        ON pir.pageId = p.id AND p.parentId IS NULL
    LEFT JOIN locale AS lr
        ON lr.languageCode = pir.languageCode AND lr.regionCode = pir.regionCode
WHERE
    (p.id = '5' AND l.isActive = 'yes')
    OR (p.parentId IS NULL AND lr.isActive = 'yes')

Answer 3

You could order by p.id=5 and use a limit of 1. It's a bit of a hack, but it'll work.

SELECT 
        p.*,   pi.languageCode,   pi.regionCode
    FROM
        page AS p
        INNER JOIN pageInfo AS pi
            ON pi.pageId = p.id
        INNER JOIN locale AS l
            ON l.languageCode = pi.languageCode AND l.regionCode = pi.regionCode
    WHERE
        (p.id = '5' OR (p.parentId IS NULL))
        AND (l.isActive = 'yes')
    ORDER BY p.id = '5' DESC
    LIMIT 1