Postgresql – How to get users how do not have a group (through join table)

postgresql

I want to get users how are not present in a group. The group name is "everybody".

I have a join table called groups_users with user_id and group_id.

I did this

SELECT users.email
FROM users
  WHERE NOT EXISTS (
    SELECT groups_users.user_id 
    FROM groups_users
    INNER JOIN groups ON groups_users.group_id = groups.id 
    WHERE groups.name = 'everybody'
);

But it doesn't return any result. I have a user without any group and it doesn't appear. Did I miss something?

Best Answer

Right now your (uncorrelated) subquery returns all user IDs, which is obviously a non-empty set. You need to correlate the subquery to the particular user from the outer query:

SELECT users.email
FROM users
  WHERE NOT EXISTS (
    SELECT groups_users.user_id 
    FROM groups_users
    INNER JOIN groups ON groups_users.group_id = groups.id 
    WHERE groups.name = 'everybody'
    AND groups_users.user_id = users.id
);

Related Solutions

PostgreSQL Joins – How to Inner Join Some Tables and Outer Join Others

It sounds like you are looking for a FULL [OUTER] JOIN. Per documentation:

FULL OUTER JOIN

First, an inner join is performed. Then, for each row in T1 that does not satisfy the join condition with any row in T2, a joined row is added with null values in columns of T2. Also, for each row of T2 that does not satisfy the join condition with any row in T1, a joined row with null values in the columns of T1 is added.

SELECT *
FROM   users           u  
JOIN   user_products   up ON u.id = up.user_id
                         AND u.id = 1
JOIN   products        p ON p.id = up.product_id
FULL   JOIN categories c ON c.id = p.category_id;

This returns all categories and also returns all user-product combinations for the given user_id.

If you are enforcing referential integrity with foreign keys and products.category_id is defined NOT NULL, so that every product is assigned to an existing category (not in your question), you can replace the FULL JOIN with a RIGHT JOIN.

Plus, either way, the condition to select a specific user has to move to a subquery or (simpler) to a JOIN condition between the first three tables. It has to be applied before the last table categories is joined.

SQL Fiddle.

Postgresql – Join table into different columns

Assuming Postgres 9.4 or later:

SELECT id, t2.table2value0, t2.table2value1, t2.table2othervalues
FROM   table1 t1
LEFT   JOIN (
   SELECT table1_id AS id
        , min(value) FILTER (WHERE type = 0) AS table2value0
        , min(value) FILTER (WHERE type = 1) AS table2value1
        , array_agg(value) FILTER (WHERE type IN (1,2) IS NOT TRUE) AS table2othervalues
   FROM   table2
   GROUP  BY table1_id
   ) t2 USING (id);

The aggregate FILTER clause was introduced with pg 9.4. There are various less elegant alternatives for older versions:

Postgres Nested WHEN Aggregate Function

This returns one row for every row in table1, missing types in table2 result in NULL values.

If there are more than one value for table2value0 and table2value1, I only want the first.

You seem to be under the impression that there is a natural order of rows, but there is not:

Does Postgres preserve insertion order of records?

I picked the minimum value instead, since your requirement is effectively undefined.

The best query depends on your actual table definition (including all constraints).

In particular, the expression type IN (1,2) IS NOT TRUE also catches type IS NULL, which may not be necessary, depending on your missing table definition.

Aside:
Your desire to return a single (scalar) value for types 1 and 2, but an array for the rest makes it harder to solve with crosstab() (but still possible). Basics:

PostgreSQL Crosstab Query

Best Answer

Related Solutions

PostgreSQL Joins – How to Inner Join Some Tables and Outer Join Others

Postgresql – Join table into different columns

Related Question