Re: how to structure queries like SELECT a.col IN ( b.col FROM b ) FROM a ?

21 Nov 2016

      Revisiting your question and query: SELECT ( CASE WHEN ( 
table_one.this_column IN ( SELECT this_column FROM table_two ) ) THEN 1 
ELSE 0 END ) AS new_column FROM table_one ;

You can use sub-queries in the select-list, but they need to return a 
scalar value (and preferably be correlated) for each row of table_one. 
So use for instance:
SELECT this_column, (SELECT COUNT(*) FROM table_two t2 WHERE 
t2.this_column = table_one.this_column) AS new_column FROM table_one;

For the derivation of whether a match of this_column value exists in t2 
or not, use:
SELECT this_column, (CASE WHEN (SELECT COUNT(*) FROM table_two t2 WHERE 
t2.this_column = table_one.this_column) = 0 THEN 0 ELSE 1 END) AS 
new_column FROM table_one;
+-------------+------------+
| this_column | new_column |
+=============+============+
|           1 |          1 |
|           2 |          1 |
|           3 |          0 |
|           4 |          0 |
|           5 |          0 |
+-------------+------------+
5 tuples (37.294ms)

You can use groupings on this query result, such as:
SELECT COUNT(*), (CASE WHEN (SELECT COUNT(*) FROM table_two t2 WHERE 
t2.this_column = table_one.this_column) = 0 THEN 0 ELSE 1 END) AS 
new_column  FROM table_one GROUP BY new_column;

This query unfortunately does not return a result in the Jun2016-SP2 
release, which is a defect. Thanks for reporting this in Bugzilla.

To (temporarily) workaround this defect problem, I found you can use a 
view. Create it via:
CREATE VIEW table_one_vw AS
SELECT this_column, (CASE WHEN (SELECT COUNT(*) FROM table_two t2 WHERE 
t2.this_column = table_one.this_column) = 0 THEN 0 ELSE 1 END) AS 
new_column FROM table_one;

and next do the grouping on the view:
SELECT COUNT(*), new_column FROM table_one_vw GROUP BY new_column;

or with multiple aggregations per group:
SELECT COUNT(*) AS count, MIN(this_column) AS min_this_column, 
MAX(this_column) AS max_this_column, AVG(this_column) AS 
avg_this_column, SUM(this_column) AS sum_this_column, new_column FROM 
table_one_vw GROUP BY new_column;
+-------+----------+----------+--------------------------+----------+----------+
| count | min_this | max_this | avg_this_column          | sum_this | 
new_colu |
:       : _column  : _column  :                          : _column  : 
mn       :
+=======+==========+==========+==========================+==========+==========+
|     2 |        1 |        2 |                      1.5 |        3 
|        1 |
|     3 |        3 |        5 |                        4 |       12 
|        0 |
+-------+----------+----------+--------------------------+----------+----------+
2 tuples (50.325ms)

These solutions give correct results, even if the values in this_column 
contain duplicates in table_one or in table_two or in both. Try it.

If you do not want to create a view (once), you can also use a WITH 
common table expression construct:
WITH table_one_cte AS (SELECT this_column, (CASE (SELECT COUNT(*) FROM 
table_two t2 WHERE t2.this_column = table_one.this_column) WHEN 0 THEN 0 
ELSE 1 END) AS new_column FROM table_one) SELECT COUNT(*) AS count, 
MIN(this_column) AS min_this_column, MAX(this_column) AS 
max_this_column, AVG(this_column) AS avg_this_column, SUM(this_column) 
AS sum_this_column, new_column FROM table_one_cte GROUP BY new_column;

Note, the below suggested join-query:

SELECT COUNT(*) AS count, (CASE WHEN t2.this_column IS NULL THEN 0 ELSE 
1 END) AS new_column
   FROM table_one t1 LEFT OUTER JOIN table_two t2 ON t1.this_column = 
t2.this_column
  GROUP BY new_column;

will NOT give your desired results if this_column values in table_two 
contain duplicates!

The below UNION ALL query however works okay, even if the values in 
this_column contain duplicates in table_one or in table_two or in both.

Martin van Dinther

On 16-11-2016 15:11, Anthony Damico wrote:
...
ooof, i guess i might need to implement a join after all.  seems like 
the cleaner option..  thanks Martin for taking the time to help me out!
On Wed, Nov 16, 2016 at 8:32 AM, dinther mailto:dinther@monetdbsolutions.com> wrote:
SELECT COUNT(*) AS count, 1 AS new_column FROM table_one
     WHERE this_column IN (SELECT this_column FROM table_two)
    UNION ALL
    SELECT COUNT(*) AS count, 0 AS new_column FROM table_one
     WHERE this_column NOT IN (SELECT this_column FROM table_two);
or with a join:
SELECT COUNT(*) AS count, (CASE WHEN t2.this_column IS NULL THEN 0
    ELSE 1 END) AS new_column
      FROM table_one t1 LEFT OUTER JOIN table_two t2 ON t1.this_column
    = t2.this_column
     GROUP BY new_column;
On 16-11-2016 12:35, Anthony Damico wrote:
...
hi monetdb team,
i've pasted a minimal reproducible example of a few SQL queries
    that i had expected to work -- only about half of them do work,
    and the other half just error with 'ParseException:SQLparser:'
my goal is to get the very last line working, so if anybody could
    suggest a hack or a workaround that doesn't involve a JOIN, i
    would appreciate it.  thanks all!!
CREATE TABLE table_one (this_column INTEGER) ;
    INSERT INTO table_one VALUES (1) ;
    INSERT INTO table_one VALUES (2) ;
    INSERT INTO table_one VALUES (3) ;
    INSERT INTO table_one VALUES (4) ;
    INSERT INTO table_one VALUES (5) ;
CREATE TABLE table_two (this_column INTEGER) ;
    INSERT INTO table_two VALUES (1) ;
    INSERT INTO table_two VALUES (2) ;
# works as expected
    SELECT SUM( ( table_one.this_column IN ( SELECT this_column FROM
    table_two ) ) ) FROM table_one ;
    SELECT MAX( ( table_one.this_column IN ( SELECT this_column FROM
    table_two ) ) ) FROM table_one ;
# fails
    SELECT ( table_one.this_column IN ( SELECT this_column FROM
    table_two ) ) AS new_column FROM table_one ;
# Server says 'ParseException:SQLparser:'.
# works
    SELECT AVG( CASE WHEN table_one.this_column IN ( SELECT
    this_column FROM table_two ) THEN 1 ELSE 0 END ) AS new_column
    FROM table_one ;
# fails
    SELECT ( CASE WHEN ( table_one.this_column IN ( SELECT
    this_column FROM table_two ) ) THEN 1 ELSE 0 END ) AS new_column
    FROM table_one ;
# Server says 'ParseException:SQLparser:'.
# fails [[this query is the one i would like to implement]]
    SELECT COUNT(*) , ( CASE WHEN ( table_one.this_column IN ( SELECT
    this_column FROM table_two ) ) THEN 1 ELSE 0 END ) AS new_column
    FROM table_one GROUP BY new_column ;
# Server says 'ParseException:SQLparser:'.
_______________________________________________
    users-list mailing list
    users-list@monetdb.org mailto:users-list@monetdb.org
    https://www.monetdb.org/mailman/listinfo/users-list
    https://www.monetdb.org/mailman/listinfo/users-list