MySQLFiddle here: http://sqlfiddle.com/#!2/12213/16
I have three tables I am trying to work with.
Table 1: users
table (stores data for each user in the database)
Table 2: users_fav
table (stores data for each user who has added another user as a favorite)
Table 3: users_rated
table (stores data for each user who has rated another user)
users_fav
and users_rated
store the users.uid
who is doing the rating or faving in the users_fav.uid
or users_rated.uid
columns and the user they are adding to users_fav.matchuid
or users_rated.matchuid
.
The end result I want:
I need to pull all the users from the users table who have not been rated OR favorited by a specified user. In the sql fiddle data the user trying to pull the results is user id 1, who has several of the other users rated and faved for testing purposes.
I have constructed the joins and tried a few things but can not get the results I want. Here is what I have tried (please reference data and comments in the fiddle posted above). It should be assumed that user id 1 is trying to pull all the users who user 1 has not already favorited or rated:
#attempt one shows no results
SELECT users.uid, users.gender, users.username
FROM users
LEFT JOIN users_fav
ON users.uid=users_fav.matchuid
LEFT JOIN users_rated
ON users.uid=users_rated.matchuid
WHERE users.uid > 0
AND users.gender = 0
AND users_fav.uid <> 1
AND users_rated.uid <> 1
ORDER BY users.uid ASC;
This shows no results at all. I've tried joining the users_fav
and users_rated
tables on uid
as well as matchuid
.
#attempt two shows all results with gender 0
SELECT users.uid, users.gender, users.username
FROM users
LEFT JOIN users_fav
ON users.uid=users_fav.matchuid AND users_fav.uid <> 1
LEFT JOIN users_rated
ON users.uid=users_rated.matchuid AND users_rated.uid <> 1
WHERE users.uid > 0
AND users.gender = 0
ORDER BY users.uid ASC;
This second query I have tried doing the exclude with the join. It seems to ignore this and returns 6 out of the 7 total users, only excluding user 1 who has a gender of 1, which is excluded in the query.
Any guidance is appreciated.
Thanks.