Quantcast

RIGHT JOIN! still not supported?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
16 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

RIGHT JOIN! still not supported?

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: RIGHT JOIN! still not supported?

Chris Locke-3
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" <[hidden email]>
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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: RIGHT JOIN! still not supported?

Daniel Kamil Kozar
In reply to this post by PICCORO McKAY Lenz
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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: RIGHT JOIN! still not supported?

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 <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: RIGHT JOIN! still not supported?

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 <[hidden email]>
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 <[hidden email]>
>> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: RIGHT JOIN! still not supported?

Chris Locke-3
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 <[hidden email]> 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 <[hidden email]>
> 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 <[hidden email]>
> >> 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
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: RIGHT JOIN! still not supported?

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
FROM        Persons P
LEFT JOIN   (           Pets Pt
            JOIN        PetAccessories 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,
</wqw>


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Chris Locke
Sent: Wednesday, March 22, 2017 11:22 AM
To: SQLite mailing list <[hidden email]>
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 <[hidden email]> 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
> <[hidden email]>
> 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
> >> <[hidden email]>
> >> 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
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: RIGHT JOIN! still not supported?

R Smith
In reply to this post by Eric Grange

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: RIGHT JOIN! still not supported?

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:[hidden email]] Im Auftrag von R Smith
Gesendet: Mittwoch, 22. März 2017 14:30
An: [hidden email]
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
[hidden email]
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: [hidden email]

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: RIGHT JOIN! still not supported?

Stephen Chrzanowski
In reply to this post by R Smith
On Wed, Mar 22, 2017 at 9:30 AM, R Smith <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: RIGHT JOIN! still not supported?

Jeffrey Mattox
In reply to this post by R Smith
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.  Here's a stackoverflow thread about this:

FULL OUTER JOIN with sqlite - Stack Overflow
http://stackoverflow.com/questions/1923259/full-outer-join-with-sqlite

Jeff

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: RIGHT JOIN! still not supported?

R Smith
In reply to this post by Hick Gunter

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:[hidden email]] Im Auftrag von R Smith
> Gesendet: Mittwoch, 22. März 2017 14:30
> An: [hidden email]
> 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
> [hidden email]
> 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: [hidden email]
>
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: RIGHT JOIN! still not supported?

Darren Duncan
In reply to this post by Chris Locke-3
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: RIGHT JOIN! still not supported?

David Raymond
In reply to this post by R Smith
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 <at the same time>. 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 <someTypeOf>join B <someTypeOf>join C" will always be "(A <someTypeOf>join B) <someTypeOf>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:[hidden email]] On Behalf Of R Smith
Sent: Wednesday, March 22, 2017 9:30 AM
To: [hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: RIGHT JOIN! still not supported?

Hick Gunter
In reply to this post by R Smith
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: [hidden email]

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: RIGHT JOIN! still not supported?

James K. Lowden
In reply to this post by Jeffrey Mattox
On Wed, 22 Mar 2017 10:53:09 -0500
Jeffrey Mattox <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...