On 2/10/2010 7:09 AM, Mike M wrote: > >>> 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 >
That may need to be an LEFT OUTER JOIN so that it returns record from the category table, whether or not there is a related record in the selection table. But I'm not sure with out a clearer understanding of your data. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:3277 Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
