I'm using PostgreSQL as my database.
CREATE TABLE groups ( id SERIAL NOT NULL, name TEXT NOT NULL, PRIMARY KEY (id), UNIQUE (name) ); CREATE TABLE users ( id SERIAL NOT NULL, group INT NOT NULL, name TEXT NOT NULL, PRIMARY KEY (id), FOREIGN KEY (group) REFERENCES groups(id), UNIQUE (group), UNIQUE (name) );
I want to create a view that includes a boolean column stating whether or not a group has any users based on the has many / belongs to relationship of the two tables above. If it's easier, I'd even be fine with an integer column stating how many users a group has in it. How can I do this in my view?
CREATE VIEW group_info AS SELECT DISTINCT g.name AS name, ???? AS empty FROM groups AS g JOIN users AS u ON (g.id = u.group) ;
I've looked into using
COUNT to help with testing the existence of a group ID in the users table, but I'm not seeing how to put it all together.