How to use selects in join clauses in Android 2.1?

I have a query that does some magical sort-group-joining that works just fine on Android 2.2 (froyo) version of SQLite:

SELECT channels.*, p.*
FROM channels 
LEFT JOIN programs p ON channels._id = p.channel_id
JOIN (select p2.channel_id, max(p2.airdate) max_air FROM programs p2 
      GROUP BY p2.channel_id) pj
ON pj.channel_id = p.channel_id AND pj.max_air = p.airdate
ORDER BY channels.channel_index asc;

For some reason, the join doesn't work with Android 2.1 but fails with

no such column: pj.channel_id

I reckon this is because SQLite has been upgraded from 2.1 to 2.2, but what would be the proper SQL that works for both versions?

Answers


The resolution was simple, I just needed to drop the alias "p":

SELECT channels.*, programs.*
FROM channels
LEFT JOIN programs ON channels._id = programs.channel_id
JOIN (select p2.channel_id channel_id, max(p2.airdate) max_air FROM programs p2
      GROUP BY p2.channel_id) pj
ON pj.channel_id = programs.channel_id AND pj.max_air = programs.airdate

Need Your Help

Nested element won't remove parent class

jquery parent-child removeclass

The class is set on an <li> by it being clicked. Now a nested element (<button>) will not remove the class from it's parent.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.