Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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;


And the query is

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

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 ?


  • Jim_KnicelyJim_Knicely Administrator


    I get a different error:

    dbadmin=> select p.playerid,
    dbadmin-> from players p, (select distinct game from matches) m
    dbadmin-> where p.playerid not in (select playerid from matches where;
    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, 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

    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
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.