how to fix corelated subquery with NOT IN

jumanjijumanji Registered User
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 Employee, Registered User, VerticaExpert

    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 Registered User
    edited March 11

    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
    ------------------------
    ....

  • jumanjijumanji Registered User

    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