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: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
