CODEWITHSUNDEEP
pythonsqlpostgresql
Friendly King
Friendly King

Reputation: 2476

Query that excludes rows in a certain table

I have three tables: users, contacts, and groups. I want to find all the contacts of a user, and then from those selected contacts, I want to exclude those contacts of that user that have a particular group_id found in the groups table.

My groups table is structured as such:

id (primary key)
group_id (a foreign key to a table contain general group info)
user_id (a foreign key to the users table)

My contacts table is structured like this:

id (primary key)
user_id (a foreign key to the `users` table of the user who added the contact)
contact_id (a foreign key to the `users` table of the added contact)

My current, not-working query is this:

"""SELECT c.*, u.*
   FROM contacts c
       LEFT JOIN groups g ON c.contact_id = g.user_id
       INNER JOIN users u on u.id = c.contact_id
   WHERE c.user_id = %s AND
       <not sure what further constraints to place on query>""", (user_id, group_id)

From my understanding, the LEFT JOIN is certainly incorrect, and given that it is incorrect, I have not yet added any more constraints in the WHERE clause.

What is the best way to accomplish this? Thank you.

Upvotes: 0

Views: 65

Answers (1)

woemler
woemler

Reputation: 7169

Assuming that the LEFT JOIN is correct and you want to include contacts who do not belong to any group, you can try the following query:

select 
    c.*,
    u.*
from users u
    join contacts c
        on u.id = c.user_id
    left join groups g
        on c.contact_id = g.user_id
where
    c.user_id = %s
    and g.group_id not in (<your groups here>)

Where your list of groups would be a comma-separated list of identifiers. I don't know if the PostgreSQL python driver contains any function for easily formatting this or not, but that is the idea.

To answer your secondary question in your comment (how to get contacts without group AND contacts in excluded group), you probably need to use a union:

select 
    c.*,
    u.*
from users u
    join contacts c
        on u.id = c.user_id
    left join groups g
        on c.contact_id = g.user_id
where
    c.user_id = %s
    and g.group_id is null
union
select 
    c.*,
    u.*
from users u
    join contacts c
        on u.id = c.user_id
    join groups g
        on c.contact_id = g.user_id
where
    c.user_id = %s
    and g.group_id = %d

Upvotes: 1

Related Questions