Forums

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

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.