'I am trying to find the sql query to get the red boxed items from the diagram. What is the query to get the red boxed columns?

enter image description here

I have been trying to the sql query for the columns in the red box, any assistance would be helpful.

I tried this query:

SELECT

Employees.LastName,

Employees.FirstName,

Region.RegionDescription

FROM

    (
    
        (
    
            (Employees 
    
                LEFT JOIN
    
                EmployeeTerritories 
    
                    ON Employees.EmployeeID = EmployeeTerritories.EmployeeID) 
    
        LEFT JOIN
    
        Territories 
    
            ON EmployeeTerritories.EmployeeID = Territories.TerritoryID) 
    
    LEFT JOIN
    
      Region 
    
        ON Territories.RegionID = Region.RegionID
    
    );
mysqlsql


Solution 1:[1]

Your join with Territories was on EmployeeID (EmployeeTerritories.EmployeeID = Territories.TerritoryID), but should be on EmployeeTerritories.TerritoryID = Territories.TerritoryID. And removed all the brackets, makes it easier to read. Does this work for you?

SELECT
  Employees.LastName,
  Employees.FirstName,
  Region.RegionDescription
FROM Employees 
LEFT JOIN EmployeeTerritories 
ON Employees.EmployeeID = EmployeeTerritories.EmployeeID
LEFT JOIN Territories 
ON EmployeeTerritories.TerritoryID = Territories.TerritoryID
LEFT JOIN Region 
ON Territories.RegionID = Region.RegionID

Solution 2:[2]

please check second join

ON EmployeeTerritories.EmployeeID = Territories.TerritoryID) should be ON EmployeeTerritories.TerritoryID = Territories.TerritoryID)

here is changed SQL

   SELECT
    Employees.LastName,    
    Employees.FirstName,    
    Region.RegionDescription    
    FROM    
        (
          (        
                (Employees         
                    LEFT JOIN
                    EmployeeTerritories         
                        ON Employees.EmployeeID = EmployeeTerritories.EmployeeID)         
            LEFT JOIN        
            Territories         
                ON EmployeeTerritories.TerritoryID = Territories.TerritoryID)         
        LEFT JOIN        
          Region         
            ON Territories.RegionID = Region.RegionID        
        );

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 VvdL
Solution 2 G.E.B

Related Questions