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 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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
