SQL Query to sellect fallback option

Question

Here is the scenario. I have the following MySQL table

CREATE TABLE IF NOT EXISTS `pages` (
`ciso` varchar(3) CHARACTER SET ascii NOT NULL DEFAULT 'AUS',
`page` varchar(24) CHARACTER SET ascii NOT NULL,
`dgroup` TINYINT(1) DEFAULT 0, 
UNIQUE KEY `page` (`page`,`ciso`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Typically it will have entries for multiple pages with ciso = 'AUS'. In some instances there will be an override entry specific to the US where the page name will be the same as for the AUS entry but with ciso = 'USA'.

What I need to do from time-to-time is this - select all page entries with a specified dgroup setting & ciso = 'USA' by preference but if no such entry exists fall back to the matching AUS entry instead. To make that a bit clearer

page   ciso  dgroup

PgA    AUS    3
PgB    AUS    3
PgC    AUS    3
PgD    AUS    4
PgB    USA    3
PgC    USA    3

In this instance the query, for dgroup = 3, should return PgA:AUS, PgB:USA and PgC:USA.

I can handle all of this with a spot of PHP but I suspect that it is possible to write a smart bit of SQL that does the job. Only, I am just not that smart when it comes to SQL. Perhaps someone here could help?

Answer 1

I don't know if I've really understand what you're searching for but according to your result, I think this query would do;

SELECT page, ciso, dgroup
FROM   pages
Where  (ciso = 'USA' OR ciso = 'AUS')
AND     dgroup = 3

where you can specify the dgroup