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

16 Nov 2016

      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
https://www.monetdb.org/mailman/listinfo/users-list

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

dinther