SQL Query to show results that don't have a relation to variable-CodePudding

For an assignment I have which includes a delete and add friend system (like Facebook), I've made a query that works by using two SQL tables, one which includes a friend_id, name and other information, and another which holds two friend_id columns, that show the relationship with the users and if they're friends.

User Table (friends)
| friend_id  | profile_name |
|:---------- |:------------:|
| 1          | John         |
| 2          | Peter        |
| 3          | Alex         |
| 4          | Nick         |
 ---------------------------

Friendship Table (myfriends)
| friend_id1 | friend_id2 |
|:---------- |:----------:|
| 1          | 3          |
| 2          | 4          |
| 3          | 1          |
| 4          | 2          |
 -------------------------

I am wanting to get a query which selects people that don't have a connection with a result (I want to show anyone who doesn't have a connection to friend_id '1', so only want to show users 2 and 4), and then display their name.

I have a query that selects the ones which have the relation which is:

SELECT friends.profile_name,friends.friend_id FROM `myfriends` JOIN `friends` ON friends.friend_id = myfriends.friend_id2 WHERE `friend_id1` = 1;

The query bellow shows all results from the table, and even using '!=', it doesn't select those who don't have a relation to friend_id '1'

SELECT friends.profile_name,friends.friend_id FROM `myfriends` JOIN `friends` ON friends.friend_id = myfriends.friend_id2 WHERE `friend_id1` != 1;

How can I fix this query so it shows all results but those connected to ‘friend_id1’ = 1

CodePudding user response：

with connected as (SELECT friend_id,
                      myfriends.friend_id2 friend
    FROM myfriends
    JOIN friends
        ON friends.friend_id = myfriends.friend_id1
    WHERE friend_id1 = 1)
    select *
    from friends 
where friend_id not in (select distinct friend from connected union all select distinct friend_id from connected)

you cannot change the where clause as it specifies which user you want to focus on. So first get the users that are connected (in the first cte), and then select all users except those found in the first result of the connected users.

By the way, your example is misleading as it can be solved with a bug by doing something simple in the join.

edit

while it wasn't clease which version you were using, (I thought with clause is available in the newer mysql versions) I created another solution that is working on mysql 5.6 and should work for you as well:

select f.*
from friends f
left join (
  SELECT friend_id, myfriends.friend_id2 friend
  FROM myfriends
  JOIN friends
    ON friends.friend_id = myfriends.friend_id1
  WHERE 1 in (friend_id,friend_id2)) f1 

  on f1.friend = f.friend_id
  where f1.friend is null

it has a nicer implementation in one part (1 in one of 2 columns), and uses a left join that takes the nulls from the right table.