SQL join in postgres

Question

I have the following tables:

user_friends
>id
>user_id
>friend_id
>status

user_fields
>id
>user_id
>party_status

user_friends is a table that list who I am friends. So, I can request a friendship and I would have my id in the user_id column or I can be already a friend of somebody else and my id would be in the friend_id column.

I want to write a query that will return all my friends that have a party_status of 'READY' in the user_fields table and that I am friends with.

Here is what I have thus far but its not getting me what I want and I am having a tough time wrapping my head around it. Any help is appreciated. I am using postgres

SELECT * FROM userfriends_friends
        INNER JOIN userapp_userfields ON    (userfriends_friends.friend_id=userapp_userfields.user_id)
        AND (userfriends_friends.friend_id=userapp_userfields.user_id)
        WHERE userfriends_friends.user_id = {user_id}
        OR userfriends_friends.friend_id = {user_id}
        AND userapp_userfields.party_status = 'READY'
        AND userfriends_friends.status = 'APPROVED'

Mariano D'Ascanio · Accepted Answer · 2016-05-08 00:40:18Z

1

Try enclosing the OR in parentheses, as in:

WHERE (userfriends_friends.user_id = {user_id}
OR userfriends_friends.friend_id = {user_id})
AND userapp_userfields.party_status = 'READY'
AND userfriends_friends.status = 'APPROVED'

Otherwise, the engine will bring you every record that has userfriends_friends.user_id = {user_id}, regardless of the other conditions.

Hope this helps!

answered May 8, 2016 at 0:40

Mariano D'Ascanio

1,2002 gold badges16 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alex Over a year ago

This does work but I ended up having to make two queries because I couldn't match the reverse relationship where friend_id was me and user_id was my friend. The reason why is party_status was returning the party_status for the incorrect person. For me instead of my friend.

Collectives™ on Stack Overflow

SQL join in postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related