Is there a better approach on this MySQL query?

The following query finds the directors of all shops. I think tha开发者_运维问答t there is a simpler solution to this, something that I will prefer to use. Thank you.

SELECT s.ShopID, (EmployeeName+' '+EmployeeSurname) AS FullName FROM shop AS s
JOIN WorksOn AS w ON s.ShopID = w.ShopID
JOIN employee AS e ON w.EmployeeID = e.EmployeeID
JOIN director AS d ON e.EmployeeID = d.EmployeeID

TABLES

employee : EmployeeID, EmployeeName,EmployeeSurname
shop: ShopID, Name, Address
WorksOn: EmployeeID, ShopID
director: EmployeeID

---

It's a very simple query. According to information you provided I don't see anything simpler.

Pardon. Yes you can do it in 2 joins:

SELECT 
    w.ShopID, (EmployeeName+' '+EmployeeSurname) AS FullName 
FROM 
    WorksOn AS w
    JOIN employee AS e ON w.EmployeeID = e.EmployeeID
    JOIN director AS d ON e.EmployeeID = d.EmployeeID

---

THere are a few practical concerns with that query. They might not be issues depending upon how you are using it. But, I'm guessing that in your case you aren't satisfied. So here are my issues:

1. if there isn't a director etc for a store you get no results for that store. LEFT JOIN it.
2. if there are 2 directors and 2 employees, you'll get 4 records. So, you can fix that by having separate queries or by GROUP BY and GROUP_CONCAT or some other aggregate function.