0

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.

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
  • This seems way too easy... but it worked perfectly! – Bryan Dec 22 '14 at 13:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.