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
