I'm using PostgreSQL as my database.
CREATE TABLE users ( id SERIAL NOT NULL, name TEXT NOT NULL, PRIMARY KEY (id), UNIQUE (name) ); CREATE TABLE groups ( id SERIAL NOT NULL, name TEXT NOT NULL, PRIMARY KEY (id), UNIQUE (name) ); CREATE TABLE associations ( user INT NOT NULL, group INT NOT NULL, admin BOOLEAN NOT NULL DEFAULT false, PRIMARY KEY (user, group), FOREIGN KEY (user) REFERENCES users(id), FOREIGN KEY (group) REFERENCES groups(id) );
Similar to another question I posted here, how can I compare the result of multiple rows from another table and aggregate them in a view, perhaps using something like
CREATE VIEW user_info AS SELECT DISTINCT u.id AS id, u.name AS name, EXISTS ( SELECT 1 FROM associations AS a WHERE (u.id = a.user AND a.admin = true) ) AS is_admin, BOOL_AND(???) AS is_complete_admin FROM users AS u ;
Here, I'm wanting the
is_complete_admin to be true if every row in
associations for a particular user has
admin = true.
Again, I think
BOOL_AND is an option here, but I'm just not sure how to put it all together.