I have the sql as below:
PRAGMA foreign_keys = 1;
CREATE TABLE people ( pid INTEGER PRIMARY KEY, name TEXT );
CREATE TABLE groups ( gid INTEGER PRIMARY KEY, name TEXT );
CREATE TABLE p_g_bridge (
pid INTEGER NOT NULL REFERENCES people,
gid INTEGER NOT NULL REFERENCES groups,
PRIMARY KEY ( pid, gid )
);
-- people
INSERT INTO people VALUES ( 1, 'Alice' );
INSERT INTO people VALUES ( 2, 'Bob' );
INSERT INTO people VALUES ( 3, 'Charlie' );
-- groups
INSERT INTO groups VALUES ( 1, 'users' );
INSERT INTO groups VALUES ( 2, 'admin' );
INSERT INTO groups VALUES ( 3, 'web' );
INSERT INTO groups VALUES ( 4, 'db' );
INSERT INTO groups VALUES ( 5, 'nobody' );
-- everyone in users
INSERT INTO p_g_bridge VALUES ( 1, 1 );
INSERT INTO p_g_bridge VALUES ( 2, 1 );
INSERT INTO p_g_bridge VALUES ( 3, 1 );
-- Alice and Bob into admin
INSERT INTO p_g_bridge VALUES ( 1, 2 );
INSERT INTO p_g_bridge VALUES ( 2, 2 );
-- Charlie into web
INSERT INTO p_g_bridge VALUES ( 3, 3 );
-- Alice and Charlie into db
INSERT INTO p_g_bridge VALUES ( 1, 4 );
INSERT INTO p_g_bridge VALUES ( 3, 4 );
and below is the table:
select * from groups;
gid name
---------- ----------
1 users
2 admin
3 web
4 db
5 nobody
sqlite> select * from people;
select * from people;
pid name
---------- ----------
1 Alice
2 Bob
3 Charlie
sqlite> select * from p_g_bridge;
select * from p_g_bridge;
pid gid
---------- ----------
1 1
2 1
3 1
1 2
2 2
3 3
1 4
3 4
and I want the query for people who all are 'users', ie) the result must as follows:
pid name
---------- ----------
1 Alice
2 Bob
3 Charlie
I write a query like this:
sqlite> select * from people where people.pid = (select p_g_bridge.pid from p_g_bridge where p_g_bridge .gid = (select groups.gid from groups where groups.name = 'users'));
IT gives the result as follows:
pid name
---------- ----------
1 Alice
But a query must return result as said above.
You want IN not = for your sub-selects. IN checks for membership, = pulls a value from the subquery (the first, the last, it's implementation specific) or it throws an error when there is more than one result.
select *
from people
where people.pid IN
(
select p_g_bridge.pid
from p_g_bridge
where p_g_bridge.gid IN
(
select groups.gid
from groups
where groups.name = 'users'
)
);