Complex SQL Join help!

Answer 1

I'm not sure that I completely understand what you're trying to do, but I think that you need to move your criteria into the LEFT OUTER JOINs. If you put it in the WHERE clause then if no match is found by the LEFT OUTER JOIN, those columns will appear as NULL and so they will fail the WHERE clause check. You are in effect turning the LEFT OUTER JOIN into an INNER JOIN.

FROM
    dbo.Accomplishment AS a
INNER JOIN dbo.AccomplishmentType AS at ON
    at.Id = a.AccomplishmentTypeId
INNER JOIN dbo.AccomplishmentArea AS aal ON
    aal.AccomplishmentId = a.Id
INNER JOIN dbo.Area AS al ON
    al.Id = aal.AreaId
INNER JOIN dbo.UserAccomplishment AS ua ON
    ua.AccomplishmentId = a.Id
INNER JOIN dbo.[User] AS u ON
    u.Id = ua.UserId
INNER JOIN dbo.UserUserGroup AS uug ON
    uug.UserId = u.Id
INNER JOIN dbo.UserGroup AS ug ON
    ug.Id = uug.UserGroupId AND
    ug.localid = 2
INNER JOIN dbo.UserGroupType AS ugt ON
    ugt.Id = ug.UserGroupTypeId
INNER JOIN dbo.UserUserGroup AS uugo ON
    uugo.UserId = u.Id
LEFT OUTER JOIN dbo.UserGroup AS ugo ON
    ugo.Id = uugo.UserGroupId AND
    ugo.localid <> 2
LEFT OUTER JOIN dbo.UserGroupType AS ugto ON
    ugto.Id = ugo.UserGroupTypeId
WHERE
    ugto.localid = 4
ORDER BY
    a.DateCreated DESC, u.LastName, u.FirstName, u.Id, UserGroupName

Answer 2

Here's what I re-wrote so far:

SELECT a.Id, a.DateCreated, a.DateModified, a.LastUpdatedBy, a.AccomplishmentTypeId, 
       a.Title, a.Description, a.ApplicableDate, a.LocalId, 
       at.Name AS AccomplishmentTypeName, a.Name AS AreaName, u.FirstName, 
       u.LastName, ug.Name AS UserGroupName, ugo.Name AS OtherUserGroupName
  FROM dbo.Accomplishment a 
  JOIN dbo.AccomplishmentType AS at ON at.Id = a.AccomplishmentTypeId 
  JOIN dbo.AccomplishmentArea AS aal ON aal.AccomplishmentId = a.Id 
  JOIN dbo.Area AS al ON al.Id = aal.AreaId 
  JOIN dbo.UserAccomplishment AS ua ON ua.AccomplishmentId = a.Id 
  JOIN dbo.[User] AS u ON u.Id = ua.UserId 
  JOIN dbo.UserUserGroup AS uug ON uug.UserId = u.Id 
  JOIN dbo.UserGroupType AS ugt ON ugt.Id = ug.UserGroupTypeId 
  JOIN dbo.UserGroup AS ug ON ug.Id = uug.UserGroupId 
  JOIN dbo.UserGroupType AS ugto ON ugto.Id = ug.UserGroupTypeId
 ORDER BY a.DateCreated DESC, u.LastName, u.FirstName, u.Id, UserGroupName

You've got a redundant join to UserUserGroup, and your joins to UserGroup can be consolidated because the LEFT JOIN criteria would cancel out what's done in the previous join to the same table.

Answer 3

You have made a classic mistake when learning left joins, you put conditions in the where clause that turn them into inner joins

Try this

SELECT     a.Id, a.DateCreated, a.DateModified, a.LastUpdatedBy, a.AccomplishmentTypeId, a.Title,
a.Description, a.ApplicableDate,  a.LocalId, at.Name AS AccomplishmentTypeName, 
a.Name AS AreaName, u.FirstName, u.LastName, ug.Name AS UserGroupName, ugo.Name AS OtherUserGroupName 
FROM        dbo.Accomplishment AS a 
INNER JOIN  dbo.AccomplishmentType AS at ON at.Id = a.AccomplishmentTypeId 
INNER JOIN  dbo.AccomplishmentArea AS aal ON aal.AccomplishmentId = a.Id 
INNER JOIN  dbo.Area AS al ON al.Id = aal.AreaId 
INNER JOIN  dbo.UserAccomplishment AS ua ON ua.AccomplishmentId = a.Id 
INNER JOIN  dbo.[User] AS u ON u.Id = ua.UserId 
INNER JOIN  dbo.UserUserGroup AS uug ON uug.UserId = u.Id 
INNER JOIN  dbo.UserGroup AS ug ON ug.Id = uug.UserGroupId 
INNER JOIN  dbo.UserGroupType AS ugt ON ugt.Id = ug.UserGroupTypeId 
INNER JOIN  dbo.UserUserGroup AS uugo ON uugo.UserId = u.Id 
LEFT OUTER JOIN  dbo.UserGroup AS ugo ON ugo.Id = uugo.UserGroupId  AND ugo.LocalId <> 2
LEFT OUTER JOIN  dbo.UserGroupType AS ugto ON ugto.Id = ugo.UserGroupTypeId  AND ugto.LocalId = 4
WHERE     ug.LocalId = 2 
ORDER BY a.DateCreated DESC, u.LastName, u.FirstName, u.Id, UserGroupName 

see this link for an explanation of this: http://wiki.lessthandot.com/index.php/WHERE_conditions_on_a_LEFT_JOIN

Answer 4

@ChaosPandion's comment about reformatting is a good idea, but perhaps you need some help understanding what good formatting is. Well, it's probably different for everyone :-) but if I was writing your query I'd format it as follows:

SELECT  TOP (100) PERCENT
          a.Id,
          a.DateCreated,
          a.DateModified,
          a.LastUpdatedBy,
          a.AccomplishmentTypeId,
          a.Title,
          a.Description,
          a.ApplicableDate,  
          a.LocalId,
          at.Name AS AccomplishmentTypeName,
          a.Name AS AreaName,
          u.FirstName,
          u.LastName,
          ug.Name AS UserGroupName,  
          ugo.Name AS OtherUserGroupName 
FROM dbo.Accomplishment AS a
INNER JOIN dbo.AccomplishmentType AS at
  ON at.Id = a.AccomplishmentTypeId
INNER JOIN dbo.AccomplishmentArea AS aal
  ON aal.AccomplishmentId = a.Id
INNER JOIN dbo.Area AS al
  ON al.Id = aal.AreaId
INNER JOIN dbo.UserAccomplishment AS ua
  ON ua.AccomplishmentId = a.Id
INNER JOIN dbo.[User] AS u
  ON u.Id = ua.UserId
INNER JOIN dbo.UserUserGroup AS uug
  ON uug.UserId = u.Id
INNER JOIN dbo.UserGroup AS ug
  ON ug.Id = uug.UserGroupId
INNER JOIN dbo.UserGroupType AS ugt
  ON ugt.Id = ug.UserGroupTypeId
INNER JOIN dbo.UserUserGroup AS uugo
  ON uugo.UserId = u.Id
LEFT OUTER JOIN dbo.UserGroup AS ugo
  ON ugo.Id = uugo.UserGroupId
LEFT OUTER JOIN dbo.UserGroupType AS ugto
  ON ugto.Id = ugo.UserGroupTypeId 
WHERE ug.LocalId = 2 AND
      ugo.LocalId <> 2 AND
      ugto.LocalId = 4
ORDER BY a.DateCreated DESC,
         u.LastName,
         u.FirstName,
         u.Id,
         UserGroupName

I think this formatting makes it easier to read.

Share and enjoy.

Answer 5

try this:

SELECT  
          a.Id,
          a.DateCreated,
          a.DateModified,
          a.LastUpdatedBy,
          a.AccomplishmentTypeId,
          a.Title,
          a.Description,
          a.ApplicableDate,  
          a.LocalId,
          at.Name AS AccomplishmentTypeName,
          a.Name AS AreaName,
          u.FirstName,
          u.LastName,
          ug.Name AS UserGroupName,  
          ugo.Name AS OtherUserGroupName 
FROM dbo.Accomplishment AS a
INNER JOIN dbo.AccomplishmentType AS at
  ON at.Id = a.AccomplishmentTypeId
INNER JOIN dbo.AccomplishmentArea AS aal
INNER JOIN dbo.Area AS al
  ON aal.AccomplishmentId = a.Id
  ON al.Id = aal.AreaId
INNER JOIN dbo.UserAccomplishment AS ua
INNER JOIN dbo.[User] AS u
INNER JOIN dbo.UserUserGroup AS uug
INNER JOIN dbo.UserGroup AS ug
INNER JOIN dbo.UserGroupType AS ugt
INNER JOIN dbo.UserUserGroup AS uugo
LEFT OUTER JOIN dbo.UserGroup AS ugo
LEFT OUTER JOIN dbo.UserGroupType AS ugto
  ON ua.AccomplishmentId = a.Id
  ON u.Id = ua.UserId
  ON uug.UserId = u.Id
  ON ug.Id = uug.UserGroupId
  ON ugt.Id = ug.UserGroupTypeId
  ON uugo.UserId = u.Id
  ON ugo.Id = uugo.UserGroupId
  ON ugto.Id = ugo.UserGroupTypeId 
WHERE ug.LocalId = 2 AND
      ugo.LocalId <> 2 AND
      ugto.LocalId = 4
ORDER BY a.DateCreated DESC,
         u.LastName,
         u.FirstName,
         u.Id,
         UserGroupName

Answer 6

You JOINs are all fine. It's the filtering!

If you write a filter that does not allow nulls in left joined tables, then your filter will remove the additional records that the left joins allow.

ugo and ugto are both reached by LEFT JOIN

WHERE (ug.LocalId = 2)
  AND (ugo.LocalId <> 2 OR ugo.LocalId is null)
  AND (ugto.LocalId = 4 OR ugto.LocalId is null) 

PS - mixing JOIN and FILTERING criteria (both in ON or both in WHERE) is a recipe for insanity. Stay sane!

---

Edit: found an error in the join:

INNER JOIN dbo.UserUserGroup AS uugo ON uugo.UserId = u.Id

Should be

LEFT JOIN dbo.UserUserGroup AS uugo ON uugo.UserId = u.Id