CODEWITHSUNDEEP
sqlpostgresqljoin
Alex
Alex

Reputation: 540

SQL join in postgres

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'

Upvotes: 0

Views: 43

Answers (1)

Mariano D'Ascanio
Mariano D'Ascanio

Reputation: 1202

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!

Upvotes: 1

Related Questions