dimanche 21 décembre 2014

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


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.





Aucun commentaire:

Enregistrer un commentaire