MSSQL outer join on 3 tables

[justsomeguy]

I need a little help here, I'm having problems wrapping my brain around this. I haven't done a lot with outer joins, so I hope someone else here has.This is a simplified example of the table structure I have to work with:

table usersuid   fname	   lname----------------------------1	 john		smith2	 jane		smith3	 englebert   humperdinktable contentcid   title-----------------------1	 goat milking2	 yak shavingtable sessionsuid   cid   score-----------------1	 1	 802	 1	 852	 2	 903	 2	 95

And this is the type of result set that I'm looking for:

expected resultuid   cid   score-----------------1	 1	 801	 2	 null2	 1	 852	 2	 903	 1	 null3	 2	 95

I know that the users table will be a left outer join, because I need all of the users (in reality, the list of users will be filtered by several conditions). I didn't show it here, but users are also assigned certain content. This example shows all users and all content, but again in reality the list of users will be filtered, and each user only has access to certain content. There may or may not be a session stored for the user and the content, but I basically need a list of all users with all of the content assigned to each and the session, if it exists, for each user's content session.This is on SQL Server 2000. I'm trying to run this query just to see what I get back, and I get a syntax error on the keyword 'where':

select c.cid as cid, c.title, u.uid as uid, u.fname, u.lname, s.total_score, s.completed from users u left outer join content_session s inner join content c on u.uid=s.uid and c.cid=s.cid where u.active=1

The syntax error occurs for anything I put after that last cid, or a syntax error on cid itself if I do not include a clause after it.Is there anyone who has done an outer join with 3 tables?

[aspnetguy]

I haven't done many outer joins but shouldn't the syntax be somehting like this?

SELECT c.cid as cid, c.title, u.uid as uid, u.fname, u.lname, s.total_score, s.completed FROM users u LEFT OUTER JOIN content_session s ON u.uid=s.uid INNER JOIN content c ON c.cid=s.cid WHERE u.active=1

[Reg Edit]

I think this is what you're looking for:

select c.cid as cid, c.title, u.uid as uid, u.fname, u.lname, s.total_score, s.completed from (users u left outer join content_session s on u.uid=s.uid) left join content c  on  c.cid=s.cid where u.active=1

[justsomeguy]

Cool, that query does execute. Thanks for pointing that out, now I just need to add all the conditions..

[justsomeguy]

OK, I'm confusing myself again. This is my updated query:

selectc.cid,c.title, c.launchcount,c.timetocomplete,u.uid,u.fname,     u.lname, s.start_date,         s.last_entered,  s.last_browser_used, s.last_date_taken, s.comment,            s.progress,      s.total_score,       s.completed, s.visited, s.aicc_lesson_status, s.aicc_score,    s.aicc_time,s.aicc_core_lesson, s.counter,            s.instructor_uid,s.remote_addr, s.complete_datefrom (users u left outer join content c )left outer join content_session s on u.uid=s.uid and s.cid=c.cidwhere c.cid in (  select cid  from user_content where user_content.uid = u.uid  union  select cid  from group_content, group_users  where group_users.uid = u.uid and group_content.gid = group_users.gid  union  select cid  from usergroup_content, usergroup_users  where usergroup_users.uid = u.uid and usergroup_content.ugid = usergroup_users.ugid  union  select cid  from group_content, usergroup_groups, usergroup_users  where usergroup_users.uid = u.uid and usergroup_groups.ugid = usergroup_users.ugid and group_content.gid = usergroup_groups.gid) and u.uid in (select uid from usergroup_users where ugid='2')order by u.uid, c.cid

This is for a learning management system (LMS), which manages online training for users. Here is a brief description of the tables I'm using in this query:users - holds users!content - content are the courses, training pieces, other pieces of online contentcontent_session - records of users launching contentuser_content - content directly assigned to a specific usergroup_content - content assigned to a content groupgroup_users - users assigned to a content groupusergroup_content - content assigned to a usergroupusergroup_users - users assigned to a usergroupusergroup_groups - content groups assigned to a usergroupThe point of this specific query is to get all of the users in a particular usergroup (in this case, usergroup '2'), and display training records for all content that has been assigned to them. A record in the content_session table only exists if the user has actually launched the content, but we need results for users and content even if they have not launched it yet (if the content has been assigned to them, but a content_session does not exist). The huge subquery with all the UNION statements gets all of the content assigned to a user. This query gives a syntax error on line 28 (probably because of the lack of an ON clause for the outer join), and a syntax error on line 60, which is the line right before the ORDER BY clause (the line starting with AND).I'm having a hard time thinking about what needs to go where. I'm trying to get something equivalent to this:select all users from the user group select all content assigned to each user (with a join) select all content sessions recorded for the user (with an outer join to make sure that we return a row for all user/content assignments)I'm getting pretty tripped up on where to put the different conditions. Eventually this needs to work for a system with over 10,000 users registered, around 250 usergroups, and around 115 pieces of content. So, that's why I'm trying to keep it all in one query.Sadly, the LMS this is a part of was never formally designed, and over the course of around 8 years and 5 versions it has essentially just been continually added onto as more customers request specific features. It was never designed (or tested) to support 10,000 users, but now we have clients with that many users, so there are some huge scalability issues we are running into. I got permission to completely redesign and rewrite the thing, but unfortunately I don't have the time to do it right now. So, I'm looking for (another) quick fix. This is a textbook example of how not to design an application, but I'm pretty sure that it is possible to do what I need to do with SQL and at least make the current version usable before I take the months that will be required to redo everything the right way.Someone else stated the problem like this:

I guess the source of the problem is that you need to limit both the users to the usergroup and the content to that which is assigned to the users in the usergroup. THEN pull in any content_session records that match...So something like this pseudo-SQL:

select  the  fields  you  needfrom  (users where uid in (select uid from usergroup_users where ugid='2'))  (content where cid in (select huge UNION list)  left outer join  content_session on uid=uid and cid=cid

But the question is: how do you make this valid SQL?!?

The answer is: " "

[justsomeguy]

If anyone's keeping score, here is the answer:

selectc.cid,c.title, c.launchcount,c.timetocomplete,u.uid,u.fname, u.lname, s.start_date, s.last_entered, s.last_browser_used, s.last_date_taken, s.comment, s.progress, s.total_score, s.completed, s.visited, s.aicc_lesson_status, s.aicc_score, s.aicc_time,s.aicc_core_lesson, s.counter, s.instructor_uid,s.remote_addr, s.complete_datefrom ( users u left outer join content c on c.cid in (    select cid    from user_content where user_content.uid = u.uid        union        select cid    from group_content, group_users    where group_users.uid = u.uid and group_content.gid = group_users.gid        union        select cid    from usergroup_content, usergroup_users    where usergroup_users.uid = u.uid and usergroup_content.ugid = usergroup_users.ugid      union        select cid    from group_content, usergroup_groups, usergroup_users    where usergroup_users.uid = u.uid and usergroup_groups.ugid = usergroup_users.ugid and group_content.gid = usergroup_groups.gid  ))left outer join content_session s on u.uid=s.uid and s.cid=c.cidwhere u.uid in (select uid from usergroup_users where ugid='2')order by u.uid, c.cid

[Guest Tramponetee]

This is your expected result:expected resultuid cid score-----------------1 1 801 2 null2 1 852 2 903 1 null3 2 95And here's the code:select u.UID, c.CID, s.Scorefrom Users ucross join Contents cleft outer join Sessions s on s.Uid = u.Uid and s.CID = c.CIDorder by u.UID