>> I want to show a list of records from one table, along with related >> data from a second table. I need every record from the first table, but >> am only interested in data from the second table that have a given user >> id. > >It sounds like you want a left join with an optional filter, something along >the lines of: > >SELECT * FROM category c >LEFT JOIN selection s ON s.CategoryID = c.CategoryID >WHERE s.UserID IS NULL OR s.UserID = [x] >ORDER BY CategoryID, s.UserID > >(Untested, but you get the idea). > > >-Justin
Right, that was my first thought as well. However, this query won't quite work. For example, say you have a record in Category with ID 5. And you have a record in Selection with categoryID = 5 and userID = Y. If you run the query above with "WHERE s.UserID is null or s.UserID = X", then you will not get any records with categoryID = 5, because the left join will give you (CategoryID=5, userID=Y). However, I do want to retrieve a record in this case, with (CategoryID=5, userID=null). That's why I'm not seeing any way around using a subquery. Thanks ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3276 Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6
