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
