Graham wrote:
In the query below, I want to obtain all options_totals table records(500 or so), except for those
users_options records where the userID=3. i.e. Not retrieve options the user has already selected.
If I understand correctly, user_options is a relational table for a many-to-many relation. That is, for each user there are rows in user_options for each option that user has selected. Hence, a particular record from options_totals may be referenced multiple times, once for each user who has selected it, yes?
Have tried many combinations of joins, but get either just the records which are in the users_options table, less userID=3, or failing that no records at all.
SELECT DISTINCT options_totals.optionID AS optionID, options.optioncode, options_totals.nameID , options_names.cname, users_options.listtype AS listtype, users_options.userstatus AS userstatus , users_options.userID AS userID FROM options_totals LEFT JOIN options_names ON options_names.ID = options_totals.nameID LEFT JOIN options ON options.ID = options_totals.optionID LEFT JOIN users_options ON options_totals.optionID = users_options.optionID WHERE userID <> 3 ORDER BY userID
The "DISTINCT" here is a red flag. I expect that you got too many results and threw that in as a "fix". It should be possible to get exactly what you want, making that unneccesary. And I cannot imagine why you'd want to order by userID.
Appreciate if someone can show me the right syntax. Graham
Simplifying to keep the focus on the join, I expect that
SELECT * FROM options_totals JOIN users_options ON options_totals.optionID = users_options.optionID WHERE userID = 3;
gets all the options_totals rows that user 3 has selected. You want the opposite, the rows user 3 has not selected. Changing the join to LEFT JOIN is the right idea, but changing the WHERE clause to userID != 3 kills you. Why? Think about what happens: The join lines up each row in options_totals with every row in users_options with a matching optionID. Making it a LEFT JOIN adds a single row for each options_totals row which has never been selected by *ANY* user. Remember that these extra rows have NULLs for all users_options columns. Now you filter the results with WHERE userID != 3. This tosses all of user 3's information, and it tosses the NULL rows as well! This is because comparisons to NULL are NULL, and only comparisons which return TRUE are matched.
What you need to do instead is include the userID = 3 as part of your join criteria, like this:
SELECT *
FROM options_totals LEFT JOIN users_options
ON options_totals.optionID = users_options.optionID AND userID = 3
WHERE userID IS NULL;This way, rows in options_totals are only lined up with user 3's rows in users_options. Because it's a LEFT JOIN, however, you still get rows from options_totals which don't have a match in users_options. Those are the rows you want, and they have all NULLs in the users_options columns. We catch those with the WHERE clause.
With that in mind, I think your full query should be
SELECT options_totals.optionID AS optionID,
options.optioncode,
options_totals.nameID,
options_names.cname,
users_options.listtype AS listtype,
users_options.userstatus AS userstatus,
users_options.userID AS userID
FROM options_totals
LEFT JOIN options_names ON options_names.ID = options_totals.nameID
LEFT JOIN options ON options.ID = options_totals.optionID
LEFT JOIN users_options
ON options_totals.optionID = users_options.optionID AND userID = 3
WHERE userID IS NULL;Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
