what better way to multiple ON conditions

Question

I've got a table called Customers. And a table Users. (MySQL 5.3)

Customers:

customerID|customerName|accountManagerID|attendantID
123       |Google      |456             |789
124       |Apple       |457             |890

Users:

userID|userName
456   |Peter Norton
457   |Paul Allen
789   |Bill Gates
890   |Steve Jobs

I would like to be able to get the following result:

ID |Customer|Manager     |Attendant
123|Google  |Peter Norton|Bill Gates

I can get by using the following query:

SELECT customerID AS 'ID'
    ,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)

But I do think horrible do it this way, moreover, I consider 'little performatic'. Can someone point me to the right direction? Thanks!

Answer 1

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 noLEFT 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 ;