Using left join with multiple conditions SQL

Question

I am trying to make an application that will display every other user that is not the logged in user or that the logged in user does not not follow (like an explore page), but I'm having trouble writing the SQL query.

My tables are set up as:

following(username1, username2)

(meaning username1 follows username2)

and

users(username, fullname, ...)

Right now I am trying to do something like:

SELECT * FROM
users u 
LEFT JOIN following f ON (
  u.username != 'loggedInUser'
  AND f.username1 = 'loggedInUser'
  AND f.username2 = u.username
)

I replace loggedInUser in a Python script.

Unfortunately, this query is currently returning all of my users, and it should not be. I'm having a lot of issues working though this logic for some reason, does anyone have some insight?

Learn what left join returns: inner join rows plus unmatched left table rows extended by nulls. Always know what inner join you want as part of a left join. — philipxy
– philipxy, Commented Jan 31, 2019 at 4:20

Pham X. Bach · Accepted Answer · 2019-01-30 09:28:33Z

1

This will display every other users that are not the logged in user and (not or) that the logged in user does not not follow (them):

SELECT * 
FROM users u 
WHERE u.username <> 'loggedInUser'
    AND NOT EXISTS (
            SELECT 1
            FROM following f
            WHERE f.username1 = 'loggedInUser'
                AND f.username2 = u.username
        );

Your query could change to an INNER JOIN to get expected result but it may be more complicated with worse performance:

SELECT DISTINCT u.* 
FROM users u 
INNER JOIN following f 
ON f.username1 <> 'loggedInUser'
    OR f.username2 <> u.username
WHERE u.username <> 'loggedInUser';

edited Jan 30, 2019 at 9:28

answered Jan 30, 2019 at 6:38

Pham X. Bach

5,4724 gold badges32 silver badges46 bronze badges

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

Comments

Anoos · Accepted Answer · 2019-01-30 06:46:29Z

1

If you need to get all the users not followed by the logged in user, just try the below script.

SELECT * FROM
users u 
where u.username in(select username2 from following where username1!= 'loggedInUser')
and u.username!='loggedInUser'

answered Jan 30, 2019 at 6:46

Anoos

1867 bronze badges

Comments

Jovan · Accepted Answer · 2019-01-30 06:40:58Z

0

SELECT * FROM
users u 
LEFT JOIN following f ON u.[insert_primary_key_here]=f.[insert_foreign_key_here] 
WHERE u.username != 'loggedInUser'
AND f.username1 = 'loggedInUser'
AND f.username2 = u.username

answered Jan 30, 2019 at 6:40

Jovan

645 bronze badges

Comments

Hamza Tahir · Accepted Answer · 2019-01-30 08:01:52Z

-1

Why don't you simply create a new column in users of bit type that will be updated when any user is being logged in . Then you just have to write a simply query that would be

Select * from users where loggedIn=1

that will return you all the users data with logged in status

answered Jan 30, 2019 at 8:01

Hamza Tahir

927 bronze badges

2 Comments

DaveyDaveDave Over a year ago

This doesn't seem to address the main point of the question which is to include the users linked in the following table.

Hamza Tahir Over a year ago

I have explained her another way of doing work and it could be helpful for her . There is multiple ways of doing the same thing and I have explained her the easy and effective way.

Collectives™ on Stack Overflow

Using left join with multiple conditions SQL

4 Answers 4

Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related