Forums

 
ForumForumPlug-in and Pla...Plug-in and Pla...MS SQL Server D...MS SQL Server D...left outer joins all written incorrect?left outer joins all written incorrect?
Previous Previous
 
Next Next
New Post
 11/9/2010 12:35 PM
 

Hello,

I was editing a view created by a new custom module and realized that the left outer joins connecting the optional values with the base table are all written incorrectly -- the base table ID is on the RIGHT side of the equal sign and not the left. I then checked one of the regular views and it seems that they are all written in the same way. A left outer join means you want to get all the values from the table on the left and only those that match from the table on the right -- so for accounts, it would look like

select * from accounts left outer join teams on accounts.team_id = teams.id

but your code for vwAccounts dated 8/2/2010 has the account id on the right side of every relationship -- like this ....

select * from            ACCOUNTS
  left outer join ACCOUNTS ACCOUNTS_PARENT
               on ACCOUNTS_PARENT.ID       = ACCOUNTS.PARENT_ID
              and ACCOUNTS_PARENT.DELETED  = 0
  left outer join TEAMS
               on TEAMS.ID                 = ACCOUNTS.TEAM_ID
              and TEAMS.DELETED            = 0
  left outer join TEAM_SETS
               on TEAM_SETS.ID             = ACCOUNTS.TEAM_SET_ID
              and TEAM_SETS.DELETED        = 0
  left outer join USERS                      USERS_ASSIGNED
               on USERS_ASSIGNED.ID        = ACCOUNTS.ASSIGNED_USER_ID
  left outer join USERS                      USERS_CREATED_BY
               on USERS_CREATED_BY.ID      = ACCOUNTS.CREATED_BY
  left outer join USERS                      USERS_MODIFIED_BY
               on USERS_MODIFIED_BY.ID     = ACCOUNTS.MODIFIED_USER_ID
  left outer join ACCOUNTS_CSTM
               on ACCOUNTS_CSTM.ID_C       = ACCOUNTS.ID

These should then all be RIGHT outer joins, isn't that correct? Thanks,

Nancy Capitanio

New Post
 11/9/2010 12:42 PM
 

never mind -- I answered my own question -- I always thought the position of the values mattered, but apparently they don't -- I tried this with the ID on both the left and the right side and got the same results. Please disregard!

New Post
 11/9/2010 10:07 PM
 

As you have just noticed, A = B is the same as B = A.

The format we use makes it easy for us read the code. We find that the liberal use of carriage returns, tabs and spaces increases readability and reduces bugs.

Previous Previous
 
Next Next
ForumForumPlug-in and Pla...Plug-in and Pla...MS SQL Server D...MS SQL Server D...left outer joins all written incorrect?left outer joins all written incorrect?