Hi Howard,
You should re-post this on the SQLite-users list <[hidden email]>, 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 > [hidden email] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-dev _______________________________________________ sqlite-users mailing list [hidden email] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users |
Maybe:
with FooOrBar as ( select case exists(select 1 from X where x = foo) when 1 then foo else bar end as FooOrBar), 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 FooOrBar; ? -----Original Message----- From: sqlite-users [mailto:[hidden email]] On Behalf Of R Smith Sent: Tuesday, April 11, 2017 1:56 PM To: [hidden email]; SQLite mailing list Subject: Re: [sqlite] [sqlite-dev] SELECT this set ELSE that set Hi Howard, You should re-post this on the SQLite-users list <[hidden email]>, 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 > [hidden email] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-dev _______________________________________________ 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 |
Free forum by Nabble | Edit this page |