How can I aggregate multiple results from another table using something like BOOL_AND?

Question

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 BOOL_AND?

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.

Answer 1

select
 u.id,
 u.name,
 bool_or (a.admin) as is_admin,
 bool_and (a.admin) as is_complete_admin
from
 users u
 inner join
 associations a on u.id = a.user
group by u.id, u.name

• Bryan: This seems way too easy... but it worked perfectly!