Hi Howard,
You should re-post this on the SQLite-users list
<sqlite-users@mailinglists.sqlite.org>, it's more appropriate for it and
you will get a lot more (and arguably better) responses from there.
(I've gone ahead and included that list for you, just remove the dev
list from replies to avoid duplication - thanks).
If I understand you properly, then what you need is a query that is
fully listing either of two (or more) sets of filtered results, but not
both / all.
Best would be to use a CTE or Sub-query set to get to something like this:
WITH A(y) AS (
SELECT Y FROM T WHERE x = 1
), B(y) AS (
SELECT Y FROM T WHERE x = 2
), C(y) AS (
SELECT Y FROM T WHERE x = 3
), K(ca, cb, cc) AS (
SELECT
(SELECT COUNT(*) FROM A) AS AV,
(SELECT COUNT(*) FROM B) AS BV,
(SELECT COUNT(*) FROM C) AS CV
)
SELECT Y FROM K,A WHERE K.ca > 0 AND K.cb = 0 AND K.cc = 0
UNION ALL
SELECT Y FROM K,B WHERE K.ca = 0 AND K.cb > 0 AND K.cc = 0
UNION ALL
SELECT Y FROM K,C WHERE K.ca = 0 AND K.cb = 0 AND K.cc > 0
;
A better method might be to simply check the existence and not
pre-render the queries in the CTE (and perhaps make it so the deciding
bit prefers A over B over C), something like this:
WITH K(ca, cb, cc) AS (
SELECT
(SELECT COUNT(*) FROM T WHERE T.x=1),
(SELECT COUNT(*) FROM T WHERE T.x=2),
(SELECT COUNT(*) FROM T WHERE T.x=3)
)
SELECT Y FROM K,T WHERE K.ca > 0 AND T.x = 1
UNION ALL
SELECT Y FROM K,T WHERE K.ca = 0 AND K.cb > 0 AND T.x = 2
UNION ALL
SELECT Y FROM K,T WHERE K.ca = 0 AND K.cb = 0 AND T.x = 3
;
(You can see one can even remove the cc term from the K CTE)
It can obviously also be simplified sans CTE into just:
SELECT Y FROM T WHERE (SELECT COUNT(*) FROM T AS TA WHERE TA.x=1) > 0
AND x = 1
UNION ALL
SELECT Y FROM T WHERE (SELECT COUNT(*) FROM T AS TA WHERE TA.x=1) = 0
AND (SELECT COUNT(*) FROM T AS TB WHERE TB.x=2) > 0 AND x = 2
UNION ALL
SELECT Y FROM T WHERE (SELECT COUNT(*) FROM T AS TA WHERE TA.x=1) = 0
AND (SELECT COUNT(*) FROM T AS TB WHERE TB.x=2) = 0 AND x = 3
Note also that SQLite Query Planner is somewhat clever in that it should
run those WHERE clause COUNT queries only once, but it's probably safer
to force that behaviour by doing the count up in the CTE as in the first
example - though I agree with James on an often-made point that one
should only ever state the relation that is correct in the SQL, and the
Query engine should be trusted with figuring out the most efficient route.
Good luck!
On 2017/04/11 2:37 AM, Howard Kapustein wrote:
I’ve got a query that I want to match a set of data based on WHERE
x=foo but if 0 matches, I want to match WHERE x=bar
Programmatically it’s
list=FindByX(x=1)
if list.isempty
list=FindByX(x=2)
return list
To make matters worse it’s actually a multi table join e.g.
SELECT * FROM A
INNER JOIN B ON A.a=B.a
INNER JOIN C ON B.b=C.b
INNER JOIN X ON C.c=X.c
WHERE X.x=?
In theory it’s logically
SELECT * FROM (
IFNULL(SELECT * FROM A…WHERE X.x=foo, SELECT * FROM A…WHERE X.x=bar);
)
SQLITE has UNION which is the wrong verb. I want foo ELSE bar, not
both, and not an interleaved result. All of just foo, or if no matches
then all of just bar.
SQLITE’s WITH ctes seem like they might help, but in the end it still
seems like >2 sub-queries to figure out the right set (foo vs bar),
and a ton of complex SQL that’s uglier and questionable if perf any
different than just doing the programmatic style with 1-2 SQL calls.
Any (sane) SQL solution for the query? Or is the programmatic approach
the best answer?
* Howard
_______________________________________________
sqlite-dev mailing list
sqlite-...@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-dev
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
