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.
So say I have two tables: Category ( CategoryID, ... ) and Selection ( CategoryID, UserID, ... ) Selection table has FK to Category.CategoryID. Its PK is CategoryID+UserID. What I need: Every record in Category. Also, for each Category record, include data from Selection table having a given UserID. For example: Category table has: CategoryID C1 C2 C3 C4 C5 Selection table has: CategoryID, UserID C1, U1 C2, U2 C4, U1 C5, U1 C5, U2 Given UserID = U1, my query should have these results: CategoryID, UserID C1, U1 C2, null C3, null C4, U1 C5, U1 I wrote this, using a subquery, and it works fine: select c.CategoryID, (select s.UserID from Selection s where s.CategoryID = c.CategoryID and UserID = 'U1') as UserID from Category c; But I would really like to create a view so that I could do something like: select * from MyView where UserID = XYZ or UserID is null; I'm quite certain the subquery approach cannot work with the view. So my first attempt at creating a view was something like this: select c.CategoryID, s.UserID, etc., from Category c left outer join Selection s on s.CategoryID = c.CategoryID; But this would not return any record with CategoryID = C2, using the sample data above. Am I missing something obvious? I'm using SQL Server... perhaps the only solution is to create a table-valued function taking the UserID as a parameter, implemented using a subquery? btw I'm happy to be rejoining the HOF lists after 5 years :) Thanks --Mike ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:3274 Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6
