[sqlite] What is the best alternative to this RIGHT OUTER JOIN ?

Hi all, Today I've stumbled on a situation where I think I really need to use a RIGHT OUTER JOIN, and looking at all the examples on the internet I could find so far, I'm not finding a way to simulate it properly using LEFT OUTER JOINs. So I thought, before I commit to an inefficient alternative

[sqlite] bug: "no such column" with UNION + subquery + GROUP BY + ORDER BY + COLLATE

Hi, the following query fails to parse, although it should be valid: sqlite> select 1 union select 1 from (select 1 as x) group by x order by 1 collate binary; Error: no such column: x originally reported by Michael Geier here:

Re: [sqlite] bug: "no such column" with UNION + subquery + GROUP BY + ORDER BY + COLLATE

On 2014/11/08 14:21, Clemens Ladisch wrote: Hi, the following query fails to parse, although it should be valid: sqlite> select 1 union select 1 from (select 1 as x) group by x order by 1 collate binary; Error: no such column: x Will call the above Version A. I don't see how that can

Re: [sqlite] What is the best alternative to this RIGHT OUTER JOIN ?

How about the direct approach: SELECT uid FROM resource WHERE uid NOT IN (SELECT resource_uid FROM event_participant, event WHERE event_participant.event_uid = event.uid AND event.shift_uid = :shift_uid AND ev

Re: [sqlite] bug: "no such column" with UNION + subquery + GROUP BY + ORDER BY + COLLATE

sqlite> select 1 union select 1 from (select 1 as x) group by x order by 1 collate binary; Error: no such column: x sqlite> select 1 from (select 1 as x) group by x; 1 sqlite> select 1 from (select 1 as x) group by x order by 1; 1 sqlite> select 1 from (select 1 as x) group by x order by 1 col

Re: [sqlite] What is the best alternative to this RIGHT OUTER JOIN ?

On Sat, 2014-11-08 at 06:23 -0700, Keith Medcalf wrote: > How about the direct approach: > > SELECT uid > FROM resource > WHERE uid NOT IN (SELECT resource_uid > FROM event_participant, event > WHERE event_participant.event_uid = event.uid >

Re: [sqlite] bug: "no such column" with UNION + subquery + GROUP BY + ORDER BY + COLLATE

RSmith wrote: > On 2014/11/08 14:21, Clemens Ladisch wrote: >> the following query fails to parse, although it should be valid: >> >>sqlite> select 1 union select 1 from (select 1 as x) group by x order by >> 1 collate binary; >>Error: no such column: x > > I don't see how that can ever be

Re: [sqlite] What is the best alternative to this RIGHT OUTER JOIN ?

On Sat, 08 Nov 2014 22:55:46 +0900 Tristan Van Berkom wrote: > So I would have to say, the "right way to do it" is the most efficient > way, the one which provides SQLite with the best indications of how > to plot an efficient query plan. Keith is suggesting that the right way to do it is neithe

Re: [sqlite] What is the best alternative to this RIGHT OUTER JOIN ?

On Sat, 2014-11-08 at 10:23 -0500, James K. Lowden wrote: > On Sat, 08 Nov 2014 22:55:46 +0900 > Tristan Van Berkom wrote: > > > So I would have to say, the "right way to do it" is the most efficient > > way, the one which provides SQLite with the best indications of how > > to plot an efficient

Re: [sqlite] What is the best alternative to this RIGHT OUTER JOIN ?

On Saturday, 8 November, 2014 06:56, Tristan Van Berkom said: >On Sat, 2014-11-08 at 06:23 -0700, Keith Medcalf wrote: >> How about the direct approach: >> >> SELECT uid >> FROM resource >> WHERE uid NOT IN (SELECT resource_uid >> FROM event_participant, event >>

Re: [sqlite] LEFT joins affect query plan for semantically equal queries

On Wednesday, 5 November, 2014 22:23, James Lowden said: >On Wed, 05 Nov 2014 08:24:47 -0700, "Keith Medcalf" >wrote: >> The two queries are different. They may end up with the same result, >> but you are asking different questions. In the first you are >> returning only matching rows. In the

Re: [sqlite] What is the best alternative to this RIGHT OUTER JOIN ?

There's nothing vague about select statements, they're logical formulas involving the data in your database and as exact as any other programming language, albeit in a very different domain. Relational databases are based on first order predicate logic and have operations that are are rigorously de

Re: [sqlite] What is the best alternative to this RIGHT OUTER JOIN ?

On Sat, 2014-11-08 at 09:46 -0700, Keith Medcalf wrote: > On Saturday, 8 November, 2014 06:56, Tristan Van Berkom > said: > > >On Sat, 2014-11-08 at 06:23 -0700, Keith Medcalf wrote: > >> How about the direct approach: > >> > >> SELECT uid > >> FROM resource > >> WHERE uid NOT IN (SELECT reso

Re: [sqlite] What is the best alternative to this RIGHT OUTER JOIN ?

On Sun, 09 Nov 2014 00:45:16 +0900 Tristan Van Berkom wrote: > While I do understand SQL as a functional language, most functional > programming I've done still has rather explicit syntax/rules, so I get > particularly uncomfortable with writing vague statements, such as > JOIN tableA, tableB WHE