I wanted to report some further investigations into my quest to be able to write the same query only once for use both directly in SQLite and again for MS Access, with hopefully not a whole lot editing required to make the same basic query work in either environment.
On the issue of what may be called the “nested JOIN” problem where Access wants to base queries on other queries, further testing and some sage advice from Tom suggests that this problem may be solved with nested SELECT’s rather than with nested JOIN’s, and the resultant SQL will work pretty much equally well in either environment. To wit, I am finding that I’m wanting to create queries that can be structured roughly as follows:
SELECT some_right_and_left_stuff FROM (SELECT some_left_stuff FROM some_tables_on_the_left) AS L LEFT JOIN (SELECT some_right_stuff FROM some_tables_on_the_right) AS R ON some_right_and_left_stuff;
Of course, both some_tables_on_the_left and some_tables_on_the_right may involve some additional JOINS, but the required parentheses and nesting seem so far to be totally compatible between Access and SQLite. But then, the RMNOCASE issue problem and the BLOB problem must be dealt with differently between Access and SQLite.
Here’s an example of a compatibility problem I didn’t expect. Suppose we replace “SELECT some_left_stuff FROM some_tables_on_the_left” from the third line of the pseudo-code with the following. It’s a real query that will run successfully on both environments. The query returns a list RM Record Numbers associated with a Named Group. In this example, it’s group #6, and I used the group number rather than the group name to avoid having to deal with text strings. Indeed, that’s why this query is so compatible – it references no text strings. I totally stole this query from Tom, by the way. I doubt I could have figured out how to do it on my own.
SELECT N.OwnerID AS Recno FROM GroupTable AS G, PersonTable AS P INNER JOIN NameTable AS N ON P.PersonID=N.OwnerID WHERE N.OwnerID >= G.StartID AND N.OwnerID <= G.EndID AND G.GroupID = 6
Next, we make the exact same query into one using nested SELECT’s. In this case, it’s totally silly to introduce an extra level of nesting just for this one query. But remember it’s ultimately our purpose to LEFT JOIN this query with another, and the best way to do the LEFT JOIN will surely be with nested SELECT’s.
SELECT L.RecNo AS RecNo FROM ( SELECT N.OwnerID AS Recno FROM GroupTable AS G, PersonTable AS P INNER JOIN NameTable AS N ON P.PersonID=N.OwnerID WHERE N.OwnerID >= G.StartID AND N.OwnerID <= G.EndID AND G.GroupID = 6 ) AS L
Again, this query “just works” in both environments and it still produces a list of the all the RM Record Numbers associated with one particular Named Group. And again the reason the query works in both environments is that it is only dealing with numeric data. So let’s confuse things by adding one text field. We will start with the original query that was not nested.
SELECT N.OwnerID AS Recno, N.Surname AS Surname FROM GroupTable AS G, PersonTable AS P INNER JOIN NameTable AS N ON P.PersonID=N.OwnerID WHERE N.OwnerID >= G.StartID AND N.OwnerID <= G.EndID AND G.GroupID = 6
Perhaps a little surprisingly, this query still works in both environments even though I have added a text string to it, namely, NameTable.Surname. But I think it’s more or less by accident that the query works in both environments without any use of COLLATE NOCASE or CAST or StrConv. Which is to say, I’m not doing any manipulation or comparison of NameTable.Surname whatsoever.
Finally, we add a text string to the nested query.
SELECT L.RecNo AS RecNo, L.Surname AS Surname FROM ( SELECT N.OwnerID AS Recno, N.Surname AS Surname FROM GroupTable AS G, PersonTable AS P INNER JOIN NameTable AS N ON P.PersonID=N.OwnerID WHERE N.OwnerID >= G.StartID AND N.OwnerID <= G.EndID AND G.GroupID = 6 ) AS L
This query still works fine in ACCESS, and indeed it’s more user friendly than before because it’s listing the person’s surname in addition to the person’s Record Number. But in SQLite the query gives one of those obnoxious “no such collation sequence: RMNOCASE” errors. I’m not doing any processing or manipulation or comparison of a text string, but there’s the error, anyway. I’m guessing that SQLite doing something that causes the collation sequence error because it’s converting the data from N.Surname in the innermost SELECT to L.Surname in the outermost SELECT. I can fix the query in SQLite by adding COLLATE NOCASE to Surname, but then the query will no longer work in Access. Apparently there is always going to be a lot of editing required to be able to have both an Access version and an SQLite version of the same query. And the query above is really simple.
Discussions & comments from Wikispaces site
UPDATE Syntax, multiple tables
02 July 2011 15:12:34
I’m ready to play with an UPDATE transaction (in a test database, of course), but I need to update one table based on the contents of another table. The examples that I can find do not seem to work in SQLite.
The following works:
I need the WHERE clause to reference another table, and I can’t figure out how to do it. Conceptually, I need the following.
UPDATE SomeTable INNER JOIN SomeOtherTable ON some_condition
It certainly seems like this sort of thing ought to be very easy to do, but I can’t figure out the syntax.
02 July 2011 20:03:16
Jerry, I wonder if Media Repair Queries might provide a solution. I banged my head against the same question and came up with that technique in the absence of anything better.
Sounds like you’re almost ready to roll out some neat stuff!