[mythtv-users] Power search help
David Caruso
dcaruso at cox.net
Wed Feb 19 04:07:03 UTC 2014
On 2/18/2014 9:54 PM, Karl Newman wrote:
> On Tue, Feb 18, 2014 at 6:42 PM, Karl Newman <newmank1 at asme.org
> <mailto:newmank1 at asme.org>> wrote:
>
> On Tue, Feb 18, 2014 at 6:25 PM, Hika van den Hoven
> <hikavdh at gmail.com <mailto:hikavdh at gmail.com>> wrote:
>
> Hoi Hika,
>
>
> Wednesday, February 19, 2014, 3:15:37 AM, you wrote:
>
>
> Hoi Karl,
>
> Wednesday, February 19, 2014, 2:54:57 AM, you wrote:
>
>
> On Tue, Feb 18, 2014 at 5:46 PM, Hika van den Hoven
> <hikavdh at gmail.com <mailto:hikavdh at gmail.com>> wrote:
> Hoi Karl,
>
>
> Wednesday, February 19, 2014, 2:30:18 AM, you wrote:
>
>
> I need some help from the SQL experts which I know lurk on
> this list. I'm trying to create a power rule to re-record
> movies (which are currently recorded) when they air on
> channels which I've marked as commercial free. I started with
> the re-record in HD rule as per
> http://www.mythtv.org/wiki/Custom_Recording#Recording_a_Rebroadcast_in_HD:_An_Advanced_Custom_Recording_Example and
> have the basic search working like this (formatted as a SQL
> query for testing, not as a power rule--ignore the title,
> starttime, channel.name <http://channel.name> columns):
>
> SELECT program.title, program.starttime, channel.name
> <http://channel.name> FROM program JOIN channel ON
> (program.chanid = channel.chanid) JOIN recordedprogram AS R
> USING (programid) JOIN channel AS C ON (R.chanid = C.chanid
> AND C.commmethod != -2) WHERE program.category_type = "movie";
>
> It's missing the "AND channel.commmethod = -2" clause to only
> pick up programs airing on comm free channels because I'm
> planning to just use the "Commercial Free" filter which I
> presume will be appended to the WHERE clause. I probably also
> need to add filters for Live TV and Deleted groups, but that's
> pretty trivial to add. This seems to work great, but the
> problem is that I have to disable duplicate matching for this
> to work, obviously, so I need to implement that in the SELECT
> statement otherwise it will keep recording the movie as long
> as the non-commercial free recording exists. Following the
> pattern in the HD re-record I tried to do this:
>
> SELECT program.title, program.starttime, channel.name
> <http://channel.name> FROM program JOIN channel ON
> (program.chanid = channel.chanid) JOIN recordedprogram AS R
> USING (programid) JOIN channel AS C ON (R.chanid = C.chanid
> AND C.commmethod != -2) LEFT OUTER JOIN recordedprogram R2
> USING (programid) JOIN channel AS C2 ON (R2.chanid = C2.chanid
> AND C2.commmethod = -2) WHERE program.category_type = "movie"
> AND R2.starttime = NULL ORDER BY title;
> The idea there is to exclude items where there is an entry for
> that programid in recordedprogram which was recorded on a
> commercial free channel (C2.commmethod = -2). The problem is
> to accomplish that I think I need to do an inner join of the
> channel table ONLY on/with the chanid from the outer joined R2
> and I don't know how to structure that. The above almost works
> but I think it's joining C2 on the entire previous selected
> row which causes it to return an empty set (because then there
> are clauses for commmethod = -2 AND commmethod != -2).
>
> So, any suggestions?
>
> Thanks,
>
> Karl
>
>
>
>
>
>
> You can try nested queries, which means doing a guery on a
> query instead of on a table.
> I'm not exactly clear on the syntax and whether mysql supports
> this. This was long ago
> on an Oracle database.
> Thanks. I played with nested queries a little bit while
> following an alternate path to duplicate matching, but my
> impression is that mysql performance can be variable with
> nested queries, and also I think it's more than I need. This
> query is very fast (0.00 sec--probably just hitting cache
> though) and I think I'm very close to a solution, I just need
> to know how to structure that one join.
>
> Karl
>
>
>
>
>
> I think you're encountering a problem I have encountered with
> joins. If any side of it gives no record you get empty.
> Even though with your own logic you would expect result.
> I probably am phrasing it badly. But the solution for me was
> to work with two independent queries or filter further
> in programming code. I think, but I'm only a self-learned in
> sql, the nested solution is your best bet.
>
> Tot mails,
> Hika mailto:hikavdh at gmail.com
>
> "Zonder hoop kun je niet leven
> Zonder leven is er geen hoop
> Het eeuwige dilemma
> Zeker als je hoop moet vernietigen om te kunnen overleven!"
>
> De lerende Mens
> --
>
>
>
>
> I'm thinking about my phrasing; I mean if either part has no
> record that satisfies the join,
> it doesn't look any further but throws it out. So any possible
> satisfying record that doesn't
> exist in any member of the join...
>
>
> I think I found the answer. My earlier experiments with
> parentheses resulted in syntax errors but now that I look closer
> at it I think I understand why. Here's what I have now:
> SELECT program.title, program.starttime, channel.name
> <http://channel.name> FROM program JOIN channel ON (program.chanid
> = channel.chanid) JOIN recordedprogram AS R USING (programid) JOIN
> channel AS C ON (R.chanid = C.chanid AND C.commmethod != -2) LEFT
> OUTER JOIN (recordedprogram R2 JOIN channel AS C2 ON (R2.chanid =
> C2.chanid AND C2.commmethod = -2)) USING (programid) WHERE
> program.category_type = "movie" AND R2.starttime = NULL;
>
> I'm still getting an empty set, though, even when I temporarily
> mark a channel with upcoming re-airings as commercial free. I need
> to experiment some more. I think maybe I need to give my outer
> joined table a name and then check that starttime = NULL on that.
>
> Karl
>
>
> Okay, this is weird. If I remove the AND R2.starttime = NULL clause,
> then I get the results that I was getting before I added the duplicate
> matching statements. However, if I add R2.starttime to the SELECTed
> columns, they all show up as NULL. So why isn't that matching when
> it's in the WHERE clause???
>
> Karl
>
>
> _______________________________________________
> mythtv-users mailing list
> mythtv-users at mythtv.org
> http://www.mythtv.org/mailman/listinfo/mythtv-users
> http://wiki.mythtv.org/Mailing_List_etiquette
> MythTV Forums: https://forum.mythtv.org
I'm not exactly a MySQL expert but in Oracle/MSSQL/Teradata NULL never
equals NULL so your join condition of R2.starttime = NULL will always
evaluate to false. Try something like R2.starttime is NULL instead.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.mythtv.org/pipermail/mythtv-users/attachments/20140218/c733545f/attachment-0001.html>
More information about the mythtv-users
mailing list