joining query

[danno80]

Hi guys, I am new to using sql and am having trouble with getting a query right

It involves 3 tables:

users - id (primary key unique)

friends - (junction table) player_id, friend_id (both indexed to users:id)

ignores - (junction table) player_id, ignored_id (both indexed to users:id)

 

I can get what I want with multiple queries but have been unable to crack getting it with one

I want to get all users who are neither friends nor ignores for a given user

so I have user with id of say 1: I want to get all users from users and then exclude those appearing under friend_id and ignored_id in the two junction tables that have player_id of 1

 

thanks if anyone can help

[fenway]

You can use a LEFT JOIN, then restict on friend_id IS NULL and ignored_id IS NULL.