mySQL: query in which all possible values are missing at least once

Question

I have a list of TV shows. Each TV show may be blacked out in 0 or more timezones. To say that a show is "blacked out" in a timezone means that the network does not have rights to air the show in that timezone. This data looks like this:

|----|---------------------|
| ID |         Show        |
|----|---------------------|
|  1 | Nightly News        |
|  2 | Primetime Sitcom    |
|  3 | Daytime Talkshow    |
|  4 | Nightly News II     |
|  5 | Daytime Talkshow II |
|  6 | Nightly News III    |
|----|---------------------|
   |
   |-----join
   |
   v
|----|----------------------|
| ID | Timezone Restriction |
|----|----------------------|
|  1 | EST                  |
|  1 | CST                  |
|  1 | PST                  |
|  2 | EST                  |
|  2 | CST                  |
|  3 | PST                  |
|  5 | CST                  |
|  5 | PST                  |
|  6 | HST                  |
|----|----------------------|

Not all shows are timezone restricted (most are not). Given this data, I need to fetch a list contains as many results as necessary in order to supply 2 shows in each timezone that are not blacked out. The results should be ordered by ID, with each timezone seeing the lowest possible unrestricted IDs.

For instance, in the above dataset, this hypothetical query would return rows 1-4, e.g:

|----|------------------|--------------|
| ID |       Show       | Restrictions |
|----|------------------|--------------|
|  1 | Nightly News     | EST,CST,PST  |
|  2 | Primetime Sitcom | EST,CST      |
|  3 | Daytime Talkshow | PST          |
|  4 | Nightly News II  | None         |
|----|------------------|--------------|

As you can see, in the above result set, all timezones have at least 2 shows which are unrestricted. A viewer in EST or CST could watch programs 3 and 4. A viewer in PST could view programs 2 and 4. A viewer in MST or HST could view programs 1 and 2.

I can't for the life of me figure out the SQL that would get at this problem (sidenote, I don't actually need the "restrictions" column in my result, that's just here for explanatory purposes).

Very nice written question. But maybe next time you could also try to provide a [**SqlFiddle**](http://sqlfiddle.com/#!9/243fa) so we can give you an answer much faster — Juan Carlos Oropeza, Sep 23 '15 at 20:23
@JuanCarlosOropeza the bare minimum would be 2 results I suppose. Technically, there's no upper bound, but as a practical matter there would never really be more than 10 results. — Matt Korostoff, Sep 23 '15 at 20:34
I already include the sqlFiddle for your problem. But Looks like an optimization problem? Not sure what process you want to follow to get the `shows` ? because I could add `ID=4` so every `timezone` have one show and then a couple of show with only one restriction so every `timezone` can watch two shows — Juan Carlos Oropeza, Sep 23 '15 at 20:37
I don't understand your example. You say you want to get a list of shows that are NOT restricted in a timezone, but you're listing the shows that ARE restricted. — Barmar, Sep 23 '15 at 20:38
@Barmar OP say `in order to supply 2 shows in each timezone that are not blacked out. ` — Juan Carlos Oropeza, Sep 23 '15 at 20:43
@Barmar I'm looking for every timezone to have at least two unrestricted shows. I am not simply looking for a list of shows with zero restrictions (that I could do fairly easily). So, for instance, in my example results set, "Daytime Talkshow" is restricted in PST. This is _not_ restricted for a viewer physically located in EST. — Matt Korostoff, Sep 23 '15 at 20:47
In your sample if you remove `ID=1` all timezones can watch at least two show, and `HST` can watch all 3. Is that a correct solution? But you can also add all six `show` and every `timezone` is happy. Or you can replace `ID = 2` for `ID = 6` and get an even better solution. — Juan Carlos Oropeza, Sep 23 '15 at 20:51

score 2 · Answer 1 · edited May 23 '17 at 11:44

Create a table that lists all the timezones. You can then CROSS JOIN this with the show list, to get all potential zones where a show could be shown. Then use a LEFT JOIN with the restrictions table to filter out the rows that match any restrictions, as described in Return row only if value doesn't exist.

SELECT s.show, z.zone
FROM shows AS s
CROSS JOIN timezones AS z
LEFT JOIN restrictions AS r ON r.id = s.id AND r.`Timezone Restriction` = z.zone
WHERE r.id IS NULL
ORDER BY z.zone, s.id

DEMO

This lists all the shows that can be shown in each timezone, not just the first 2. See Using LIMIT within GROUP BY to get N results per group? for how to restrict the number of results per group.

score 0 · Accepted Answer · answered Sep 23 '15 at 22:51

So having thought about this a bit more, I'm pretty sure the thing I want to do is 1) lookup a list of unrestricted shows for each timezone and 2) UNION them all together. This actually seems like pretty much exactly the usecase UNION was created for now that I think of it.

So I can get a single timezones unrestricted shows like so:

SELECT `shows`.`ID`
FROM shows 
LEFT JOIN restrictions
ON `shows`.`ID`=`restrictions`.`ID`
AND `shows`.`ID` NOT IN (
  SELECT `restrictions`.`ID`
  FROM restrictions 
  WHERE `Timezone Restriction`='EST'
) 
LIMIT 2

And then just chain them together like so:

(SELECT `shows`.`ID` FROM shows  LEFT JOIN restrictions  ON `shows`.`ID`=`restrictions`.`ID`  AND `shows`.`ID` NOT IN (select `restrictions`.`ID` from restrictions where `Timezone Restriction`='EST') LIMIT 2)
UNION
(SELECT `shows`.`ID` FROM shows  LEFT JOIN restrictions  ON `shows`.`ID`=`restrictions`.`ID`  AND `shows`.`ID` NOT IN (select `restrictions`.`ID` from restrictions where `Timezone Restriction`='CST') LIMIT 2)
UNION
(SELECT `shows`.`ID` FROM shows  LEFT JOIN restrictions  ON `shows`.`ID`=`restrictions`.`ID`  AND `shows`.`ID` NOT IN (select `restrictions`.`ID` from restrictions where `Timezone Restriction`='MST') LIMIT 2)
UNION
(SELECT `shows`.`ID` FROM shows  LEFT JOIN restrictions  ON `shows`.`ID`=`restrictions`.`ID`  AND `shows`.`ID` NOT IN (select `restrictions`.`ID` from restrictions where `Timezone Restriction`='PST') LIMIT 2)
UNION
(SELECT `shows`.`ID` FROM shows  LEFT JOIN restrictions  ON `shows`.`ID`=`restrictions`.`ID`  AND `shows`.`ID` NOT IN (select `restrictions`.`ID` from restrictions where `Timezone Restriction`='HST') LIMIT 2)
ORDER BY ID;

Building on top of the sqlfiddle @Barmar supplied: http://www.sqlfiddle.com/#!9/25773/1/0

That SqlFiddle result return 5 show? So dont understand the logic on how this solve the problem — Juan Carlos Oropeza, Sep 24 '15 at 16:08

mySQL: query in which all possible values are missing at least once

2 Answers2