Return to Answer

There's nothing wrong with the query regarding the joins and how to find the values (except some details). It should be efficient if there are appropriate indexes. There is nothing "unperformatic" about it.

Things that need addressing:

• Syntax issues:

  • use the correct columns for joining (c.accountManagerID, c.attendantID, u1.userID, etc).
  • there is no LEFT INNER JOIN. There are LEFT OUTER JOIN and INNER JOIN (which can be written respectively as LEFT JOIN and JOIN).
  • add the table's name or alias in each column reference (c.customerID, not customerID). This won't always cause an error but when you'll have columns from multiple tables with same name, it helps avoiding them.
  • don't use single quotes for aliases, only for string literals. For aliases, remove them or use back quotes (`alias`) if necessary. MySQL allows it but it's confusing and even the official documentation suggests to avoid using single quotes for aliases.

• Logic issues:

  • use LEFT JOIN if the two columns (accountManagerID and attendantID) are nullable (i.e. if there are customers with no manager or no attendant assigned). Otherwise, with INNER joins, you will not see such customers in the results.

        SELECT c.customerID        AS ID
             , c.customerName      AS Customer
             , u1.accountManagerID AS Manager
             , u2.attendantID      AS Attendant
        FROM Customers c 
            LEFT JOIN Users u1 ON u1.userID = c.accountManagerID 
            LEFT JOIN Users u2 ON u2.userID = c.attendantID ;
    

There's nothing wrong with the query regarding the joins and how to find the values (except some details). It should be efficient if there are appropriate indexes. There is nothing horrible or "unperformatic" about it.

Things that need addressing:

• Syntax issues:

  • use the correct columns for joining (c.accountManagerID, c.attendantID, u1.userID, etc).
  • there is no LEFT INNER JOIN. There are LEFT OUTER JOIN and INNER JOIN (which can be written respectively as LEFT JOIN and JOIN).
  • add the table's name or alias in each column reference (c.customerID, not customerID). This won't always cause an error but when you'll have columns from multiple tables with same name, it helps avoiding them.
  • don't use single quotes for aliases, only for string literals. For aliases, remove them or use back quotes (`alias`) if necessary. MySQL allows it but it's confusing and even the official documentation suggests to avoid using single quotes for aliases.

• Logic issues:

  • use LEFT JOIN if the two columns (accountManagerID and attendantID) are nullable (i.e. if there are customers with no manager or no attendant assigned). Otherwise, with INNER joins, you will not see such customers in the results.

        SELECT c.customerID        AS ID
             , c.customerName      AS Customer
             , u1.accountManagerID AS Manager
             , u2.attendantID      AS Attendant
        FROM Customers c 
            LEFT JOIN Users u1 ON u1.userID = c.accountManagerID 
            LEFT JOIN Users u2 ON u2.userID = c.attendantID ;
    

• use the correct columns for joining (c.accountManagerID, c.attendantID, u1.userID, etc).

• there is no LEFT INNER JOIN. There are LEFT OUTER JOIN and INNER JOIN (which can be written respectively as LEFT JOIN and JOIN).

• use LEFT JOIN if these two columns are nullable (i.e. if there are customers with no manager or no attendant assigned). Otherwise, with INNER joins, you will not see such customers in the results.

• don't use single quotes for aliases, only for string literals. For aliases, remove them or use back quotes (`alias`) if necessary.

• add the table's name or alias in each column reference (c.customerID, not customerID).

    SELECT c.customerID        AS ID
         , c.customerName      AS Customer
         , u1.accountManagerID AS Manager
         , u2.attendantID      AS Attendant
    FROM Customers c 
        LEFT JOIN Users u1 ON u1.userID = c.accountManagerID 
        LEFT JOIN Users u2 ON u2.userID = c.attendantID ;
  

• Syntax issues:

  • use the correct columns for joining (c.accountManagerID, c.attendantID, u1.userID, etc).
  • there is no LEFT INNER JOIN. There are LEFT OUTER JOIN and INNER JOIN (which can be written respectively as LEFT JOIN and JOIN).
  • add the table's name or alias in each column reference (c.customerID, not customerID). This won't always cause an error but when you'll have columns from multiple tables with same name, it helps avoiding them.
  • don't use single quotes for aliases, only for string literals. For aliases, remove them or use back quotes (`alias`) if necessary. MySQL allows it but it's confusing and even the official documentation suggests to avoid using single quotes for aliases.

• Logic issues:

  • use LEFT JOIN if the two columns (accountManagerID and attendantID) are nullable (i.e. if there are customers with no manager or no attendant assigned). Otherwise, with INNER joins, you will not see such customers in the results.

        SELECT c.customerID        AS ID
             , c.customerName      AS Customer
             , u1.accountManagerID AS Manager
             , u2.attendantID      AS Attendant
        FROM Customers c 
            LEFT JOIN Users u1 ON u1.userID = c.accountManagerID 
            LEFT JOIN Users u2 ON u2.userID = c.attendantID ;
    

There's nothing wrong with the query regarding the joins and how to find the values (except some other irrelevant details). It should be efficient if there are appropriate indexes.

SELECT c. customerID AS ID
     , c.customerName AS Customer
     , u1.accountManagerID AS Manager
     , u2.attendantID AS Attendant
FROM Customers c 
    LEFT JOIN Users u1 ON u1.customerID = c.customerID 
    LEFT JOIN Users u2 ON u2.customerID = c.customerID ;

There's nothing wrong with the query regarding the joins and how to find the values (except some details). It should be efficient if there are appropriate indexes. There is nothing "unperformatic" about it.

Things that need addressing:

• use the correct columns for joining (c.accountManagerID, c.attendantID, u1.userID, etc).

• there is no LEFT INNER JOIN. There are LEFT OUTER JOIN and INNER JOIN (which can be written respectively as LEFT JOIN and JOIN).

• use LEFT JOIN if these two columns are nullable (i.e. if there are customers with no manager or no attendant assigned). Otherwise, with INNER joins, you will not see such customers in the results.

• don't use single quotes for aliases, only for string literals. For aliases, remove them or use back quotes (`alias`) if necessary.

• add the table's name or alias in each column reference (c.customerID, not customerID).

    SELECT c.customerID        AS ID
         , c.customerName      AS Customer
         , u1.accountManagerID AS Manager
         , u2.attendantID      AS Attendant
    FROM Customers c 
        LEFT JOIN Users u1 ON u1.userID = c.accountManagerID 
        LEFT JOIN Users u2 ON u2.userID = c.attendantID ;