CODEWITHSUNDEEP
mysqljoin
Sed
Sed

Reputation: 6431

Join returning zero elements where it should not

The below SQL statement looks for friendship between two user elements, by querying friendship table using users' profile_id#s, but the line I recently added to the working statement was LEFT OUTER JOIN block_user_filters AS blockedusers on blockedusers.profile_id_1 = 'abcde2' where I want to see if user abcde2 has blocked anyone, and if so I want to filter these friendships from the tableon the where clause LEFT OUTER JOIN block_user_filters AS blockedusers on blockedusers.profile_id_1 = 'abcde2' where I have the block table populated with a block row of user abcde2 to another friended user element, however the whole statement is returning 0 rows. Please help me fix this if you can. Thank you

SELECT  
  users1.username AS firstusername, 
  users2.username AS secondusername,
  users1.profile_id AS firstprofid,
  users2.profile_id AS secondprofid,
  users1.picup AS firstpicup, 
  users2.picup AS secondpicup
FROM  `users` 
  LEFT OUTER JOIN  `friendship` 
        ON friendship.profile_id_1 = users.profile_id OR friendship.profile_id_2 = users.profile_id
  LEFT OUTER JOIN users AS users1 ON users1.profile_id = friendship.profile_id_1
  LEFT OUTER JOIN users AS users2 ON users2.profile_id = friendship.profile_id_2
  LEFT OUTER JOIN block_user_filters AS blockedusers on blockedusers.profile_id_1 = 'abcde2'
  WHERE users.profile_id = 'abcde2' and blockedusers.profile_id_1 != 'abcde2' and friendship.state = 1 limit 6

EDIT: Thanks for the answer and comments, but unfortunately even after trying is null, it's still returns zero rows, I was thinking that I should be checking blockedusers.profile_id_2 instead and separate the two possibilities of friendship table's first user existance (in either friendship.profid_1 or _2), and combine them with a UNION, but this has even weirder results, I just need to get my head together... 

SELECT users1.username AS firstusername, users.username AS secondusername, 
users1.profile_id AS firstprofid, users.profile_id AS secondprofid,
users1.picup AS firstpicup, users.picup AS secondpicup
FROM  `users` 
LEFT OUTER JOIN  `friendship` ON friendship.profile_id_2 = users.profile_id
LEFT OUTER JOIN users AS users1 ON users1.profile_id = friendship.profile_id_1
LEFT OUTER JOIN block_user_filters AS blockedusers on blockedusers.profile_id_1 = 'abcde2'
WHERE users.profile_id = 'abcde2' and blockedusers.profile_id_2 != friendship.profile_id_1 and friendship.state = 1
UNION
SELECT users.username AS firstusername, users.username AS secondusername, 
users.profile_id AS firstprofid, users2.profile_id AS secondprofid,
users.picup AS firstpicup, users2.picup AS secondpicup
FROM  `users` 
LEFT OUTER JOIN  `friendship` ON friendship.profile_id_1 = users.profile_id
LEFT OUTER JOIN users AS users2 ON users2.profile_id = friendship.profile_id_2
LEFT OUTER JOIN block_user_filters AS blockedusers on blockedusers.profile_id_1 = 'abcde2'
WHERE users.profile_id = 'abcde2' and blockedusers.profile_id_2 != friendship.profile_id_2 and friendship.state = 1

Upvotes: 0

Views: 87

Answers (2)

Tom
Tom

Reputation: 6663

I think this will do what you are looking for. The last line of the last join should match on the second ID in the friendship. If there is a match, it will be omitted by the blockedusers.profile_id_1 IS NULL criteria in the WHERE clause.

SELECT users1.username   AS firstusername, 
       users2.username   AS secondusername, 
       users1.profile_id AS firstprofid, 
       users2.profile_id AS secondprofid, 
       users1.picup      AS firstpicup, 
       users2.picup      AS secondpicup 

FROM   friendship 

       LEFT OUTER JOIN users AS users1 
                    ON users1.profile_id = friendship.profile_id_1 
       LEFT OUTER JOIN users AS users2 
                    ON users2.profile_id = friendship.profile_id_2 
       LEFT OUTER JOIN block_user_filters AS blockedusers 
                    ON  blockedusers.profile_id_1 = 'abcde2'
                    AND blockedusers.profile_id_2 IN (users1.profile_id, users2.profile_id)

WHERE  (friendship.profile_id_1 = 'abcde2' or friendship.profile_id_2 = 'abcde2')
       AND friendship.state = 1 
       AND blockedusers.profile_id_1 IS NULL 

LIMIT  6

Upvotes: 1

Tom
Tom

Reputation: 6663

You need to change your where clause. blockedusers.profile_id_1 != 'abcde2' was filtering out all results. Since if there was a match in the blockedusers table, the value of profile_id_1 was 'abcde2'. If there was no match, the value returned for that field would be NULL, and the WHERE clause would fail also. Check to see if profile_id_1 IS NULL instead to get friends that are not blocked.

SELECT users1.username   AS firstusername, 
       users2.username   AS secondusername, 
       users1.profile_id AS firstprofid, 
       users2.profile_id AS secondprofid, 
       users1.picup      AS firstpicup, 
       users2.picup      AS secondpicup 
FROM   users 
       LEFT OUTER JOIN friendship 
                    ON friendship.profile_id_1 = users.profile_id 
                        OR friendship.profile_id_2 = users.profile_id 
       LEFT OUTER JOIN users AS users1 
                    ON users1.profile_id = friendship.profile_id_1 
       LEFT OUTER JOIN users AS users2 
                    ON users2.profile_id = friendship.profile_id_2 
       LEFT OUTER JOIN block_user_filters AS blockedusers 
                    ON blockedusers.profile_id_1 = 'abcde2' 
WHERE  users.profile_id = 'abcde2' 
       AND blockedusers.profile_id_1 IS NULL 
       AND friendship.state = 1 
LIMIT  6

Upvotes: 0

Related Questions