Re: [sqlite] RIGHT JOIN! still not supported?

2017-03-23 Thread James K. Lowden
On Wed, 22 Mar 2017 10:53:09 -0500
Jeffrey Mattox  wrote:

> Isn't it possible to get the same results of a RIGHT JOIN by using
> two selects with a UNION or UNION ALL between them.

Yes.  By definition, an outer join is the union of

the rows that meet the matching criteria
and
the rows that do not meet the matching criteria

The outer join construct exists for convenience: the convenience of the
user, who expresses the idea with fewer words, and the convenience of
the implementer, whose query optimization problem is simplified by the
direct expression of the desired result.  

An outer join says explicitly it wants all rows in the outer table.  On
the other hand, the query planner has to be quite sophisticated to
recognize that 

A = (A where exists B)  union (A where not exists B)

--jkl
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] RIGHT JOIN! still not supported?

2017-03-23 Thread Hick Gunter
After some thinking I came up with this:

First, set up the example (note: no CTE as I am still runnning SQLIte 3.7.14.1):

CREATE temp TABLE stock(id, cid, sid);
CREATE temp TABLE clients(id,name);
CREATE temp TABLE suppliers(id,name);
insert into stock (id) values (1),(2),(3),(4),(5),(6),(7);
UPDATE stock SET cid = id WHERE id < 4;
UPDATE stock SET sid = id - 1 WHERE id BETWEEN 2 AND 5;
INSERT INTO clients   SELECT cid, 'Hick'||cid   FROM stock WHERE cid IS NOT 
NULL;
INSERT INTO suppliers SELECT sid, 'Smith'||sid FROM stock WHERE sid IS NOT NULL;


The desired output contains rows that have a NULL client, so may as well create 
a view for this (NOTE: renaming the columns to indicate the table they are 
from):

create temp view clients_null as select id as cid,name as cname from clients 
union all select null, null;


The same holds for rows that have a NULL supplier, so create a view for this 
too (NOTE: renaming as above):

create temp view suppliers_null as select id as sid,name as sname from 
suppliers union all select null, null;


Now we are ready to produce all possible rows, may as well do this with a view 
too:

create temp view all_rows as select * from clients_null, suppliers_null;


And for the grand finale, join with stock to retrieve
1) stock connecting client to supplier
2) client with stock not connected to supplier
3) supplier with stock not connected to client
(NOTE: not a single LEFT or RIGHT join required)
(NOTE: if we could indicate that NULL == NULL should be true, just the first 
expressions should suffice):

select cname,sname,id from all_rows a join stock s on ((a.sid = s.sid) and 
(a.cid = s.cid)) or ((a.cid = s.cid) and (a.sid is null) and (s.sid is NULL)) 
or ((a.sid = s.sid) and (a.cid is null) and (s.cid is NULL));

cname|sname|id
Hick1|NULL|1
Hick2|Smith1|2
Hick3|Smith2|3
NULL|Smith3|4
NULL|Smith4|5


Adding a dummy value instead of NULL yields (NOTE: where clause to eliminate 
totally unconnected stock items):

select cname,sname,id from all_rows a join stock s on (ifnull(a.sid,-1) = 
ifnull(s.sid,-1)) and (ifnull(a.cid,-1) = ifnull(s.cid,-1)) where cname not 
null or sname not null;


Pursuing this idea further leads to:

drop view clients_null;
drop view suppliers_null;
create temp view clients_null as select id as cid,name as cname from clients 
union all select -1, null;
create temp view suppliers_null as select id as sid,name as sname from 
suppliers union all select -1, null;
create temp view stock_dummy as select id,ifnull(sid,-1) as sid,ifnull(cid,-1) 
as cid from stock;

and the quite readable statement:

select cname,sname,id from all_rows a join stock_dummy s on (a.sid = s.sid) and 
(a.cid = s.cid) where cname not null or sname not null;



___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] RIGHT JOIN! still not supported?

2017-03-22 Thread David Raymond
Just to be sure, have you actually checked your right join syntax on a system 
that supports it? Because I don't think what you have written there will 
actually achieve what you think it will.

There is no three-way join operator that will perform a left and a right join 
. No matter how you word it, one has to happen first and the 
other second.

Using the small sample you gave in one of the emails...

clients left join stock right join suppliers
(clients left join stock...) right join suppliers
(all clients but only stock related to a client) right join suppliers

(Hick1|1
Hick2|2
Hick3|3)

right join suppliers

Hick1|1|Smith1
Hick2|2|Smith2
Hick3|3|Smith3
NULL|NULL|Smith4

You need a union in there to actually accomplish what you're looking for.

I put the small schema into Access, which does allow for right joins, and when 
putting in your query I get:

"The SQL statement could not be executed because it contains ambiguous outer 
joins. To force one of the joins to be performed first, create a separate query 
that performs the first join and then include that query in your SQL statement."

So yeah, right join will not allow you to perform 2 joins at the same time. You 
need a union in there.

Since every join type has the same precedence this isn't the case of "2 + 2 / 2"

"A join B join C" will always be "(A join 
B) join C"

"A LeftJoin B RightJoin C" will always be "(A LeftJoin B) RightJoin C"...
which is the same as "C LeftJoin (A LeftJoin B)"

So you can always re-write a right join as a left join, you're not losing any 
power.
(It may be annoying to rewrite yes, but not any less functional)

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of R Smith
Sent: Wednesday, March 22, 2017 9:30 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] RIGHT JOIN! still not supported?

...
The obvious solution was:
SELECT clients.name, suppliers.name
   FROM clients
   LEFT JOIN stock ON stock.client = clients.id
   RIGHT JOIN suppliers ON stock.supplier =  suppliers.id
  WHERE (clients.name IS NOT NULL) OR (suppliers.name IS NOT NULL)
;

I suppose this could have worked too:
SELECT clients.name, suppliers.name
   FROM suppliers
   LEFT JOIN stock ON stock.supplier = suppliers.id
   RIGHT JOIN clients ON stock.client =  clients.id
  WHERE (clients.name IS NOT NULL) OR (suppliers.name IS NOT NULL)
;

or even this:
SELECT clients.name, suppliers.name
   FROM stock
   RIGHT JOIN suppliers ON stock.supplier = suppliers.id
   RIGHT JOIN clients ON stock.client =  clients.id
  WHERE (clients.name IS NOT NULL) OR (suppliers.name IS NOT NULL)
;


I don't see how to achieve that with simple joins in sqlite3. We used 
some CTEs to LEFT JOIN stock to suppliers, and then left-joined that CTE 
on the stock to the clients - which would also work using sub-query 
joins or filtered cross-joins (much slower).

So to add to the discussion: Yeah, we've needed it, but overcoming the 
problem was not exactly difficult and the use case itself is certainly 
not very common (or even mildly common, it's rather uncommon).

Cheers,
Ryan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] RIGHT JOIN! still not supported?

2017-03-22 Thread Darren Duncan
Unless I misunderstand the desired result, this query would be better formulated 
using 2 left joins instead, like this:


  SELECT ...
  FROM Persons LEFT JOIN Pets ... LEFT JOIN PetAccessories ...

-- Darren Duncan

On 2017-03-22 2:22 AM, Chris Locke wrote:

An interesting discussion of it on StackOverflow...
http://stackoverflow.com/questions/689963/does-anyone-use-right-outer-joins

To give one example where a RIGHT JOIN may be useful.

Suppose that there are three tables for People, Pets, and Pet Accessories.
People may optionally have pets and these pets may optionally have
accessories.

If the requirement is to get a result listing all people irrespective of
whether or not they own a pet and information about any pets they own that
also have accessories.


All in all probably easiest to use a RIGHT JOIN

SELECT P.PersonName,
   Pt.PetName,
   Pa.AccessoryName
FROM   Pets Pt
   JOIN PetAccessories Pa
 ON Pt.PetName = Pa.PetName
   RIGHT JOIN Persons P
 ON P.PersonName = Pt.PersonName;

Though if determined to avoid this another option would be to introduce a
derived table that can be left joined to.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] RIGHT JOIN! still not supported?

2017-03-22 Thread R Smith


On 2017/03/22 4:14 PM, Hick Gunter wrote:

Since LEFT JOIN and RIGHT JOIN  while also swapping the tables are 
interchangeable, why should this not work?

SELECT ... FROM clients LEFT JOIN ON ... ( suppliers LEFT JOIN stock ON ...) 
WHERE ...;


HI Gunter,
They are interchangeable for 1 vs. 1 join queries, but as soon as you 
add a second lookup, they are not.
Your query won't work because this will not show ANY items that are NOT 
explicitly part of the clients table. This is the problem with left-joins.


Your query will produce output like this (from my example below):

.  |  | 
.   A  | NULL | NULL
.   B  | NULL |   1
.   C  | NULL | NULL
.   D  |   $  |   2
.   E  |   &  |   4

You can see how that is different from what we needed in the original post.

To save time, I can distill the problem for you to the basics in a script:
--
-- SET-UP:
CREATE TABLE stock(id, cid, sid);

CREATE TABLE clients(id,name);

CREATE TABLE suppliers(id,name);

WITH CC(id) AS (SELECT 1 UNION ALL SELECT id+1 FROM CC WHERE id<7)
INSERT INTO stock(id) SELECT id FROM CC;

UPDATE stock SET cid = id WHERE id < 4;

UPDATE stock SET sid = id - 1 WHERE id BETWEEN 2 AND 5;

INSERT INTO clients   SELECT cid, 'Hick'||cid   FROM stock WHERE cid IS 
NOT NULL;


INSERT INTO suppliers SELECT sid, 'Smith'||sid FROM stock WHERE sid IS 
NOT NULL;


-- Show content:

SELECT * FROM stock;
  --  id  | cid| sid
  --  | -- | --
  --   1  | 1  | NULL
  --   2  | 2  | 1
  --   3  | 3  | 2
  --   4  | NULL   | 3
  --   5  | NULL   | 4
  --   6  | NULL   | NULL
  --   7  | NULL   | NULL

SELECT * FROM clients;
  --  id  | name
  --  | ---
  --   1  | Hick1
  --   2  | Hick2
  --   3  | Hick3

SELECT * FROM suppliers;
  --  id  | name
  --  | 
  --   1  | Smith1
  --   2  | Smith2
  --   3  | Smith3
  --   4  | Smith4

-- One cumbersome solution to get the correct output:
SELECT clients.name, suppliers.name, stock.id
  FROM clients
  LEFT JOIN stock ON stock.cid=clients.id
  LEFT JOIN suppliers ON stock.sid = suppliers.id
UNION
SELECT clients.name, suppliers.name, stock.id
  FROM suppliers
  LEFT JOIN stock ON stock.sid=suppliers.id
  LEFT JOIN clients ON stock.cid = clients.id
ORDER BY 3
;

  -- name| name |  id
  -- --- |  | 
  -- Hick1   | NULL |   1
  -- Hick2   | Smith1   |   2
  -- Hick3   | Smith2   |   3
  -- NULL| Smith3   |   4
  -- NULL| Smith4   |   5


-- Gunter's suggested query (I think, unless I got it wrong):
SELECT clients.name, A.name, stock.id
  FROM clients
  LEFT JOIN stock ON stock.cid = clients.id
  LEFT JOIN (SELECT stock.id, name FROM suppliers LEFT JOIN stock ON 
stock.sid = suppliers.id) AS A ON A.id = stock.id

 ORDER BY 3
;

  -- name| name |  id
  -- --- |  | 
  -- Hick1   | NULL |   1
  -- Hick2   | Smith1   |   2
  -- Hick3   | Smith2   |   3

-- Cleanup;
DROP TABLE suppliers;
DROP TABLE clients;
DROP TABLE stock;

As you can see, they are not the same - Smith3 & 4 never shows up in the 
second query. If you can say how to get to the correct query using 
simple joins (without the Union which is slow on large tables because of 
duplication, and without CTEs), I'd be both impressed and thankful.





-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von R Smith
Gesendet: Mittwoch, 22. März 2017 14:30
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] RIGHT JOIN! still not supported?


On 2017/03/22 9:53 AM, Eric Grange wrote:

For the sake of curiosity, is anyone (as in any human) using RIGHT JOIN?

Personally I never had a need for a RIGHT JOIN, not because of
theoretical or design considerations, but it just never came into my
flow of thought when writing SQL...

I guess some automated SQL query generators could use it though,
because they do not have a "flow of thought".

I had a need of it the other day (something that can easily be overcome with a 
CTE or subquery, but still may have made the sql read more natural):

We have one system which contains (among other things) clients, suppliers and 
stock tables.

A stock item will mostly have a supplier and mostly will have an intended 
client (the production is bespoke) except for a few common materials.

So we needed a list of all suppliers linked to all clients affected by them 
(via Stock), and also see the clients who are not affected by any suppliers 
(which should really be a minimum number) and the suppliers who are not 
affecting any clients (which should also be a small list).
Note that not all suppliers may be linked to live stock items, and not all 
clients may be li

Re: [sqlite] RIGHT JOIN! still not supported?

2017-03-22 Thread Stephen Chrzanowski
On Wed, Mar 22, 2017 at 9:30 AM, R Smith  wrote:

>
> On 2017/03/22 9:53 AM, Eric Grange wrote:
>
>> For the sake of curiosity, is anyone (as in any human) using RIGHT JOIN?
>>
>> Personally I never had a need for a RIGHT JOIN, not because of theoretical
>> or design considerations, but it just never came into my flow of thought
>> when writing SQL...
>>
>> I guess some automated SQL query generators could use it though, because
>> they do not have a "flow of thought".
>>
>
> I had a need of it the other day (something that can easily be overcome
> with a CTE or subquery, but still may have made the sql read more natural):
>

I've also needed it for reasons I can't remember, but I know I've used the
functionality.  RIGHT JOIN is a pretty limited use case, but handy when
needed.  I think this OP came from another DBMS software, such as MS, that
does support RIGHT JOIN.  (At least from what memory serves when I took my
MS courses back when MSSQL2K was just coming out)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] RIGHT JOIN! still not supported?

2017-03-22 Thread Hick Gunter
Since LEFT JOIN and RIGHT JOIN  while also swapping the tables are 
interchangeable, why should this not work?

SELECT ... FROM clients LEFT JOIN ON ... ( suppliers LEFT JOIN stock ON ...) 
WHERE ...;

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von R Smith
Gesendet: Mittwoch, 22. März 2017 14:30
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] RIGHT JOIN! still not supported?


On 2017/03/22 9:53 AM, Eric Grange wrote:
> For the sake of curiosity, is anyone (as in any human) using RIGHT JOIN?
>
> Personally I never had a need for a RIGHT JOIN, not because of
> theoretical or design considerations, but it just never came into my
> flow of thought when writing SQL...
>
> I guess some automated SQL query generators could use it though,
> because they do not have a "flow of thought".

I had a need of it the other day (something that can easily be overcome with a 
CTE or subquery, but still may have made the sql read more natural):

We have one system which contains (among other things) clients, suppliers and 
stock tables.

A stock item will mostly have a supplier and mostly will have an intended 
client (the production is bespoke) except for a few common materials.

So we needed a list of all suppliers linked to all clients affected by them 
(via Stock), and also see the clients who are not affected by any suppliers 
(which should really be a minimum number) and the suppliers who are not 
affecting any clients (which should also be a small list).
Note that not all suppliers may be linked to live stock items, and not all 
clients may be linked to live stock items either - but they all need to show up 
in the list.

If I can try my hand at a schematic of the sets showing links:
clients:   [ A  B  C  D E   ]
-  links| | |
stock: [1 2  3  4  5]
-  links  |  |  |
suppliers: [ @  # $  %  & ! ]

I would need to see a "linking" query result showing overlap and non-links like 
this:
. clnt | supp| stck
.  |  | 
.   A  | NULL | NULL
.   B  | NULL|   1
.   C  | NULL| NULL
.   D  |   $  |   2
.   E  |   &  |   4
. NULL |   @ | NULL
. NULL |   # | NULL
. NULL |   % |   3
. NULL |   ! | NULL

Note that I don't actually need the stck column, just adding it to show more 
sensible results.
Also note that items linked to neither client nor supplier need not show up 
(stock item 5 in this case), so I only need results having either a client or a 
supplier.


The obvious solution was:
SELECT clients.name, suppliers.name
   FROM clients
   LEFT JOIN stock ON stock.client = clients.id
   RIGHT JOIN suppliers ON stock.supplier =  suppliers.id
  WHERE (clients.name IS NOT NULL) OR (suppliers.name IS NOT NULL) ;

I suppose this could have worked too:
SELECT clients.name, suppliers.name
   FROM suppliers
   LEFT JOIN stock ON stock.supplier = suppliers.id
   RIGHT JOIN clients ON stock.client =  clients.id
  WHERE (clients.name IS NOT NULL) OR (suppliers.name IS NOT NULL) ;

or even this:
SELECT clients.name, suppliers.name
   FROM stock
   RIGHT JOIN suppliers ON stock.supplier = suppliers.id
   RIGHT JOIN clients ON stock.client =  clients.id
  WHERE (clients.name IS NOT NULL) OR (suppliers.name IS NOT NULL) ;


I don't see how to achieve that with simple joins in sqlite3. We used some CTEs 
to LEFT JOIN stock to suppliers, and then left-joined that CTE on the stock to 
the clients - which would also work using sub-query joins or filtered 
cross-joins (much slower).

So to add to the discussion: Yeah, we've needed it, but overcoming the problem 
was not exactly difficult and the use case itself is certainly not very common 
(or even mildly common, it's rather uncommon).

Cheers,
Ryan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] RIGHT JOIN! still not supported?

2017-03-22 Thread R Smith


On 2017/03/22 9:53 AM, Eric Grange wrote:

For the sake of curiosity, is anyone (as in any human) using RIGHT JOIN?

Personally I never had a need for a RIGHT JOIN, not because of theoretical
or design considerations, but it just never came into my flow of thought
when writing SQL...

I guess some automated SQL query generators could use it though, because
they do not have a "flow of thought".


I had a need of it the other day (something that can easily be overcome 
with a CTE or subquery, but still may have made the sql read more natural):


We have one system which contains (among other things) clients, 
suppliers and stock tables.


A stock item will mostly have a supplier and mostly will have an 
intended client (the production is bespoke) except for a few common 
materials.


So we needed a list of all suppliers linked to all clients affected by 
them (via Stock), and also see the clients who are not affected by any 
suppliers (which should really be a minimum number) and the suppliers 
who are not affecting any clients (which should also be a small list). 
Note that not all suppliers may be linked to live stock items, and not 
all clients may be linked to live stock items either - but they all need 
to show up in the list.


If I can try my hand at a schematic of the sets showing links:
clients:   [ A  B  C  D E   ]
-  links| | |
stock: [1 2  3  4  5]
-  links  |  |  |
suppliers: [ @  # $  %  & ! ]

I would need to see a "linking" query result showing overlap and 
non-links like this:

. clnt | supp| stck
.  |  | 
.   A  | NULL | NULL
.   B  | NULL|   1
.   C  | NULL| NULL
.   D  |   $  |   2
.   E  |   &  |   4
. NULL |   @ | NULL
. NULL |   # | NULL
. NULL |   % |   3
. NULL |   ! | NULL

Note that I don't actually need the stck column, just adding it to show 
more sensible results.
Also note that items linked to neither client nor supplier need not show 
up (stock item 5 in this case), so I only need results having either a 
client or a supplier.



The obvious solution was:
SELECT clients.name, suppliers.name
  FROM clients
  LEFT JOIN stock ON stock.client = clients.id
  RIGHT JOIN suppliers ON stock.supplier =  suppliers.id
 WHERE (clients.name IS NOT NULL) OR (suppliers.name IS NOT NULL)
;

I suppose this could have worked too:
SELECT clients.name, suppliers.name
  FROM suppliers
  LEFT JOIN stock ON stock.supplier = suppliers.id
  RIGHT JOIN clients ON stock.client =  clients.id
 WHERE (clients.name IS NOT NULL) OR (suppliers.name IS NOT NULL)
;

or even this:
SELECT clients.name, suppliers.name
  FROM stock
  RIGHT JOIN suppliers ON stock.supplier = suppliers.id
  RIGHT JOIN clients ON stock.client =  clients.id
 WHERE (clients.name IS NOT NULL) OR (suppliers.name IS NOT NULL)
;


I don't see how to achieve that with simple joins in sqlite3. We used 
some CTEs to LEFT JOIN stock to suppliers, and then left-joined that CTE 
on the stock to the clients - which would also work using sub-query 
joins or filtered cross-joins (much slower).


So to add to the discussion: Yeah, we've needed it, but overcoming the 
problem was not exactly difficult and the use case itself is certainly 
not very common (or even mildly common, it's rather uncommon).


Cheers,
Ryan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] RIGHT JOIN! still not supported?

2017-03-22 Thread Vladimir Vissoultchev
You don't need derived tables, just use brackets for explicitly order the 
execution of JOIN operators like this:

SELECT  P.PersonName
, Pt.PetName
, Pa.AccessoryName
FROMPersons P
LEFT JOIN   (   Pets Pt
JOINPetAccessories Pa
ON  Pt.PetName = Pa.PetName)
ON  P.PersonName = Pt.PersonName;

JOIN ordering is handy especially if you have to LEFT JOIN more tables to 
Persons, then using RIGHT JOIN obfuscates the code unnecessary. IMO this 
ordering pattern comes up often in reporting queries.

And this reminds me of "How much is 2 + 2 * 2?" puzzle on calculator vs excel 
:-))

cheers,



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Chris Locke
Sent: Wednesday, March 22, 2017 11:22 AM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] RIGHT JOIN! still not supported?

An interesting discussion of it on StackOverflow...
http://stackoverflow.com/questions/689963/does-anyone-use-right-outer-joins

To give one example where a RIGHT JOIN may be useful.

Suppose that there are three tables for People, Pets, and Pet Accessories.
People may optionally have pets and these pets may optionally have accessories.

If the requirement is to get a result listing all people irrespective of 
whether or not they own a pet and information about any pets they own that also 
have accessories.


All in all probably easiest to use a RIGHT JOIN

SELECT P.PersonName,
   Pt.PetName,
   Pa.AccessoryName
FROM   Pets Pt
   JOIN PetAccessories Pa
 ON Pt.PetName = Pa.PetName
   RIGHT JOIN Persons P
 ON P.PersonName = Pt.PersonName;

Though if determined to avoid this another option would be to introduce a 
derived table that can be left joined to.

On Wed, Mar 22, 2017 at 7:53 AM, Eric Grange <egra...@glscene.org> wrote:

> For the sake of curiosity, is anyone (as in any human) using RIGHT JOIN?
>
> Personally I never had a need for a RIGHT JOIN, not because of 
> theoretical or design considerations, but it just never came into my 
> flow of thought when writing SQL...
>
> I guess some automated SQL query generators could use it though, 
> because they do not have a "flow of thought".
>
> On Tue, Mar 21, 2017 at 9:50 PM, Darren Duncan 
> <dar...@darrenduncan.net>
> wrote:
>
> > What benefit does a RIGHT JOIN give over a LEFT JOIN?  What queries 
> > are more natural to write using the first rather than the second?
> >
> > While I can understand arguments based on simple mirror parity, eg 
> > we
> have
> > < so we should have > too, lots of other operations don't have 
> > mirror syntax either.
> >
> > -- Darren Duncan
> >
> > On 2017-03-21 8:42 AM, Daniel Kamil Kozar wrote:
> >
> >> Seeing how SQLite was created in 2000, it seems like nobody really 
> >> needed this feature for the last 17 years enough in order to 
> >> actually implement it.
> >>
> >> Last I heard, patches are welcome on this mailing list. Don't keep 
> >> us waiting.
> >>
> >> Kind regards,
> >> Daniel
> >>
> >> On 20 March 2017 at 21:09, PICCORO McKAY Lenz 
> >> <mckaygerh...@gmail.com>
> >> wrote:
> >>
> >>> i got this
> >>>
> >>> Query Error: RIGHT and FULL OUTER JOINs are not currently 
> >>> supported Unable to execute statement
> >>>
> >>> still today in 21 ts century?
> >>>
> >>> Lenz McKAY Gerardo (PICCORO)
> >>> http://qgqlochekone.blogspot.com
> >>>
> >>
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] RIGHT JOIN! still not supported?

2017-03-22 Thread Chris Locke
An interesting discussion of it on StackOverflow...
http://stackoverflow.com/questions/689963/does-anyone-use-right-outer-joins

To give one example where a RIGHT JOIN may be useful.

Suppose that there are three tables for People, Pets, and Pet Accessories.
People may optionally have pets and these pets may optionally have
accessories.

If the requirement is to get a result listing all people irrespective of
whether or not they own a pet and information about any pets they own that
also have accessories.


All in all probably easiest to use a RIGHT JOIN

SELECT P.PersonName,
   Pt.PetName,
   Pa.AccessoryName
FROM   Pets Pt
   JOIN PetAccessories Pa
 ON Pt.PetName = Pa.PetName
   RIGHT JOIN Persons P
 ON P.PersonName = Pt.PersonName;

Though if determined to avoid this another option would be to introduce a
derived table that can be left joined to.

On Wed, Mar 22, 2017 at 7:53 AM, Eric Grange  wrote:

> For the sake of curiosity, is anyone (as in any human) using RIGHT JOIN?
>
> Personally I never had a need for a RIGHT JOIN, not because of theoretical
> or design considerations, but it just never came into my flow of thought
> when writing SQL...
>
> I guess some automated SQL query generators could use it though, because
> they do not have a "flow of thought".
>
> On Tue, Mar 21, 2017 at 9:50 PM, Darren Duncan 
> wrote:
>
> > What benefit does a RIGHT JOIN give over a LEFT JOIN?  What queries are
> > more natural to write using the first rather than the second?
> >
> > While I can understand arguments based on simple mirror parity, eg we
> have
> > < so we should have > too, lots of other operations don't have mirror
> > syntax either.
> >
> > -- Darren Duncan
> >
> > On 2017-03-21 8:42 AM, Daniel Kamil Kozar wrote:
> >
> >> Seeing how SQLite was created in 2000, it seems like nobody really
> >> needed this feature for the last 17 years enough in order to actually
> >> implement it.
> >>
> >> Last I heard, patches are welcome on this mailing list. Don't keep us
> >> waiting.
> >>
> >> Kind regards,
> >> Daniel
> >>
> >> On 20 March 2017 at 21:09, PICCORO McKAY Lenz 
> >> wrote:
> >>
> >>> i got this
> >>>
> >>> Query Error: RIGHT and FULL OUTER JOINs are not currently supported
> >>> Unable to execute statement
> >>>
> >>> still today in 21 ts century?
> >>>
> >>> Lenz McKAY Gerardo (PICCORO)
> >>> http://qgqlochekone.blogspot.com
> >>>
> >>
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] RIGHT JOIN! still not supported?

2017-03-22 Thread Eric Grange
For the sake of curiosity, is anyone (as in any human) using RIGHT JOIN?

Personally I never had a need for a RIGHT JOIN, not because of theoretical
or design considerations, but it just never came into my flow of thought
when writing SQL...

I guess some automated SQL query generators could use it though, because
they do not have a "flow of thought".

On Tue, Mar 21, 2017 at 9:50 PM, Darren Duncan 
wrote:

> What benefit does a RIGHT JOIN give over a LEFT JOIN?  What queries are
> more natural to write using the first rather than the second?
>
> While I can understand arguments based on simple mirror parity, eg we have
> < so we should have > too, lots of other operations don't have mirror
> syntax either.
>
> -- Darren Duncan
>
> On 2017-03-21 8:42 AM, Daniel Kamil Kozar wrote:
>
>> Seeing how SQLite was created in 2000, it seems like nobody really
>> needed this feature for the last 17 years enough in order to actually
>> implement it.
>>
>> Last I heard, patches are welcome on this mailing list. Don't keep us
>> waiting.
>>
>> Kind regards,
>> Daniel
>>
>> On 20 March 2017 at 21:09, PICCORO McKAY Lenz 
>> wrote:
>>
>>> i got this
>>>
>>> Query Error: RIGHT and FULL OUTER JOINs are not currently supported
>>> Unable to execute statement
>>>
>>> still today in 21 ts century?
>>>
>>> Lenz McKAY Gerardo (PICCORO)
>>> http://qgqlochekone.blogspot.com
>>>
>>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] RIGHT JOIN! still not supported?

2017-03-21 Thread Darren Duncan
What benefit does a RIGHT JOIN give over a LEFT JOIN?  What queries are more 
natural to write using the first rather than the second?


While I can understand arguments based on simple mirror parity, eg we have < so 
we should have > too, lots of other operations don't have mirror syntax either.


-- Darren Duncan

On 2017-03-21 8:42 AM, Daniel Kamil Kozar wrote:

Seeing how SQLite was created in 2000, it seems like nobody really
needed this feature for the last 17 years enough in order to actually
implement it.

Last I heard, patches are welcome on this mailing list. Don't keep us waiting.

Kind regards,
Daniel

On 20 March 2017 at 21:09, PICCORO McKAY Lenz  wrote:

i got this

Query Error: RIGHT and FULL OUTER JOINs are not currently supported
Unable to execute statement

still today in 21 ts century?

Lenz McKAY Gerardo (PICCORO)
http://qgqlochekone.blogspot.com


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] RIGHT JOIN! still not supported?

2017-03-21 Thread Daniel Kamil Kozar
Seeing how SQLite was created in 2000, it seems like nobody really
needed this feature for the last 17 years enough in order to actually
implement it.

Last I heard, patches are welcome on this mailing list. Don't keep us waiting.

Kind regards,
Daniel

On 20 March 2017 at 21:09, PICCORO McKAY Lenz  wrote:
> i got this
>
> Query Error: RIGHT and FULL OUTER JOINs are not currently supported
> Unable to execute statement
>
> still today in 21 ts century?
>
> Lenz McKAY Gerardo (PICCORO)
> http://qgqlochekone.blogspot.com
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] RIGHT JOIN! still not supported?

2017-03-20 Thread Chris Locke
Sqlite is public domain, so feel free to add the necessary code, and once
approved, it'll get added to the main code.


Thanks,
Chris

On 20 Mar 2017 8:09 p.m., "PICCORO McKAY Lenz" 
wrote:

> i got this
>
> Query Error: RIGHT and FULL OUTER JOINs are not currently supported
> Unable to execute statement
>
> still today in 21 ts century?
>
> Lenz McKAY Gerardo (PICCORO)
> http://qgqlochekone.blogspot.com
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] RIGHT JOIN! still not supported?

2017-03-20 Thread PICCORO McKAY Lenz
i got this

Query Error: RIGHT and FULL OUTER JOINs are not currently supported
Unable to execute statement

still today in 21 ts century?

Lenz McKAY Gerardo (PICCORO)
http://qgqlochekone.blogspot.com
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users