Discussions

Expand all | Collapse all

minus operator in mapd

  • 1.  minus operator in mapd

    Posted 13 days ago
    Hi
      
       i have created two tables table1 and table2
    In table1 under id column i have values as 1,2,3
    In table2 under id column i have vlaues as 2,3,4

    so if i use minus of table1 from table2 it should return values 1
    Now i query as :
                     select id from table1 minus select id from table2;
                     
    i have error as "Exception: Parse failed: MINUS is not allowed under the current SQL conformance level"

    is there any function to use this operation for reference http://www.mysqltutorial.org/mysql-minus/


    #Core
    #Immerse


  • 2.  RE: minus operator in mapd

    Posted 13 days ago
    Hi @Nagulan Santhi,

    Sets operand like MNIUSD aren't implemented yet, but for your particular case it's better to use a NOT in with a subquery

    select id
    from table1
    where table1.id not in (select id from table2)

    using (not) IN is more flexible than minus because you can do every operation you want in a query, like aggregations, different projections between the two queries etc.

    select dimension,sum(measure)
    from table1
    where table1.id not in (select id from table2)

    if the field you are using on table2 has a lot of duplicates consider placing a distinct in the subquery

    select id
    from table1
    where table1.id not in (select distinct id froom table2)

    Hopes this help


  • 3.  RE: minus operator in mapd

    Posted 13 days ago
    Hi @Candido Dessanti

    yes we can do this if we have to minus only single column but, if we need this for more than two column in a table then how can we do this





  • 4.  RE: minus operator in mapd

    Posted 13 days ago
    Hi,
    is there any way to query this in mapd


  • 5.  RE: minus operator in mapd

    Posted 13 days ago
    Edited by Candido Dessanti 13 days ago
    @Nagulan Santhi​,

    Yes and No.

    Not yet, because the 4.8.1 lacks the functionality needed, but it should be possible on 5.0, that's going to be released soon (I've to ask if it's going to be officially supported)

    You can do this way

    select filed1,field2
    from table1 where rowid not in 
    (select table1.rowid 
     from table1 join table2 on table1.field1 = table2.field1
     and table1.field2=table2.field2)