We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


how to fix corelated subquery with NOT IN — Vertica Forum

how to fix corelated subquery with NOT IN

create table players(name varchar(32),playerid int);
insert into players values("a",1);
insert into players values("b",2);
insert into players values("c",3);
insert into players values("d",4);
insert into players values("e",5);
insert into players values("f",6);

select * from players;

create table matches(playerid int,game varchar(32));
insert into matches values(1,"game1");
insert into matches values(2,"game1");
insert into matches values(3,"game1");
insert into matches values(1,"game2");
insert into matches values(2,"game2");
insert into matches values(3,"game2");
insert into matches values(4,"game3");
insert into matches values(5,"game2");

select * from matches;

commit;

And the query is

select p.playerid,m.game
from players p, (select distinct game from matches) m
where p.playerid not in (select playerid from matches where game=m.game)

I get following error

[Vertica]VJDBC ERROR: Relation "players" does not exist [SQL State=42V01, DB Errorcode=4566]

same thing is also mentioned in the Vertica document.

How can I rewrite this query ?

Comments

  • Jim_KnicelyJim_Knicely - Select Field - Administrator

    Hi,

    I get a different error:

    dbadmin=> select p.playerid,m.game
    dbadmin-> from players p, (select distinct game from matches) m
    dbadmin-> where p.playerid not in (select playerid from matches where game=m.game);
    ERROR 2795:  Correlated subquery with NOT IN is not supported
    

    Are you trying to find the missing matches, i.e., games a player didn't play?

    Maybe something like this can work?

    dbadmin=> select p.playerid, m.game from players p cross join (select distinct game from matches) m
    dbadmin-> minus
    dbadmin-> select * from matches
    dbadmin-> order by 1, 2;
     playerid | game
    ----------+-------
            1 | game3
            2 | game3
            3 | game3
            4 | game1
            4 | game2
            5 | game1
            5 | game3
            6 | game1
            6 | game2
            6 | game3
    (10 rows)
    
  • jumanjijumanji
    edited March 2018

    Sorry I posted the wrong error message. Yes the correct error message is what you got
    Correlated subquery with NOT IN is not supported. I need results like

    Result:
    ------------------------
    game1 | 4,5,6
    ------------------------
    game2 | 4,6
    ------------------------
    game3 | 1,2,3,5,6
    ------------------------
    ....

  • Hi Jim_knicely,
    Perfect, that is the answer. I get correct results. Thanks Much! really appreciate it.
    Will share this thread on stackverflow, where I posted a copy of the same question.
    So that, it will be helpful for others too.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file