Thank you Pavel for the lesson. I learned to never underestimate the power of self join.
Works :) --- On Tue, 6/16/09, Pavel Ivanov <paiva...@gmail.com> wrote: > From: Pavel Ivanov <paiva...@gmail.com> > Subject: Re: [sqlite] Rather SQL quiestion > To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> > Date: Tuesday, June 16, 2009, 11:34 AM > Try this: > > select c.* > from players a, registrations b, players > c > where a.device_id = b.device_id > and b.mesg_token='123456' > and a.table_group_id = c.table_group_id > and a.table_id = c.table_id > > > Pavel > > On Tue, Jun 16, 2009 at 11:25 AM, Boris Ioffe<bioff...@yahoo.com> > wrote: > > > > Hello folks, > > I am having trouble understanding one very important > concept about IN operator. Let me show you my situation > below. > > > > sqlite> select sqlite_version(); > > sqlite_version() > > ---------------- > > 3.3.6 > > > ********************************************************* > > Here is my table schema > > > > CREATE TABLE players > > (device_id varchar(40) NOT NULL, > table_group_id integer, table_id integer, > > role integer, create_ts TIMESTAMP DEFAULT > CURRENT_TIMESTAMP, > > > > FOREIGN KEY (table_id,table_group_id) > REFERENCES tables(id, group_id), > > FOREIGN KEY (device_id) REFERENCES > users(device_id), > > PRIMARY KEY (device_id, table_group_id, > table_id)); > > > > > > > > > ********************************************************************* > > select table_group_id , table_id from players join > registrations on players.device_id = registrations.device_id > where mesg_token='123456'; > > > > table_group_id table_id > > -------------- ---------- > > 1 1 > > 1 2 > > > > Now I need to select all players who happened to be > playing at the same table (table_id,table_group_id) > REFERENCES tables(id, group_id). > > > > > > Obviously something like that doesn't work: > > > > select * from players where (table_group_id,table_id) > in (select table_group_id , table_id from players join > registrations on players.device_id = registrations.device_id > where mesg_token='123456') > > > > -It would work only with ONE field. > > > > select * from players where table_group_id in (select > table_group_id from players join registrations on > players.device_id = registrations.device_id where > mesg_token='123456') and table_group_id in (select table_id > ...) > > > > it is not quite the same as it will not match the > pair. > > Same puzzles me with self join route. I hate to > mindlessly traverse all pairs using code, rather having > one transaction. > > > > I appreciate any help and suggestions. > > Thanks, > > -B > > > > > > > > > > > > > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
