CODEWITHSUNDEEP
mysqlinner-joinalias
Luke
Luke

Reputation: 527

MySQL Query - inner join on multiple fields from the same relating table

Tables Stores has information about stores and its Lead and Assistant cashiers. LeadCashier and AssistantCashier are selected from table Employees.

Stores

StoreID | StoreName | LeadCashierID | AssistantCashierID
001     | Store1    |      1        |         2
002     | Store2    |      1        |         3
003     | Store3    |      2        |         3

Employees

EmployeeID | EmployeeName
1          | John
2          | Paul
3          | Steve

I need to set up a query that would display StoreID, LeadCashier's Name and AssistantCashier's name. I would build query similar to this, however I can't figure out how to echo the LeadCashier's name and AssistantCashier's name in a single query.

SELECT StoreID, StoreName, EmployeeName FROM Stores
JOIN Employees ON Employees.EmployeeID = Stores.LeadCashierID 
JOIN Employees ON Employees.EmployeeID = Stores.AssistantCashierID

Upvotes: 0

Views: 81

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269593

You simply need table aliases:

SELECT s.StoreID, s.StoreName, le.EmployeeName as LeadName,
       ae.EmployeeName as AssistantName
FROM s.Stores JOIN
     Employees le
     ON le.EmployeeID = s.LeadCashierID JOIN
     Employees ae
     ON ae.EmployeeID = s.AssistentCashierID;

Tip: Use table abbreviations for the table aliases. They make the query easier to follow.

Upvotes: 1

Related Questions