how to fix corelated subquery with NOT IN

jumanji

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 ?

Jim_Knicely

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)

jumanji

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

jumanji

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.