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.
could work...
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_fav.uid IS NULL AND users_rated.uid IS NULL
this will give you all who are not rated AND not faved (eventually you want OR?)
Will give you the result of all users who have not been rated AND been fav'd by UID = 1. For some reason this fiddle thingy won't show NULL values is you compare it to != 1. Never noticed in normal sql, so no clue if this was expected :S
SELECT *
FROM users u
LEFT JOIN users_fav f on f.matchuid = u.uid
LEFT JOIN users_rated r on r.matchuid = u.uid
WHERE
(f.uid != 1 OR
f.uid IS NULL) AND
(r.uid != 1 OR
r.uid IS NULL);