CODEWITHSUNDEEP
sqlouter-join
MJM
MJM

Reputation: 45

Using left join with multiple conditions SQL

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?

Upvotes: 2

Views: 131

Answers (4)

Pham X. Bach
Pham X. Bach

Reputation: 5432

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';

Upvotes: 1

Hamza Tahir
Hamza Tahir

Reputation: 92

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

Upvotes: -1

Anoos
Anoos

Reputation: 186

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'

Upvotes: 1

Jovan
Jovan

Reputation: 64

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

Upvotes: 0

Related Questions