Search code examples
sqlsql-server-2012adventureworks

Creating a SQL Server 2012 Query with a SUBQUERY and a JOIN statement, need a second pair of eyes?


I am trying to code an SQL Server 2012 Query using the AdventureWorks 2012 database as part of a school assignment and I created my query but i am unsure if it is clean enough and properly configured.

I am supposed to Use a Subquery and a Join statement together.

"Return the customerid & territory id from the Customer table where the name on the SalesTerritory table is ‘Central’"

I am not asking for help to cheat in any way, i just need a second pair of eyes because i am having a tough time figuring this out, I am barely able to program and have little love for it.

Here is my Query so far:

SELECT Sales.Customer.CustomerID
       , Sales.Customer.TerritoryID,Sales.SalesTerritory.Name
FROM  Sales.Customer 
INNER JOIN Sales.SalesTerritory 
    ON Sales.Customer.TerritoryID = Sales.SalesTerritory.TerritoryID
WHERE (Sales.SalesTerritory.Name = N'central')

Solution

  • SELECT Sales.Customer.CustomerID, Sales.Customer.TerritoryID,    SalesTerritoryFiltered.Name
    FROM   Sales.Customer 
    INNER JOIN (SELECT *
                FROM Sales.SalesTerritory
                WHERE Sales.SalesTerritory.Name = N'central') SalesTerritoryFiltered
      ON Sales.Customer.TerritoryID  = SalesTerritoryFiltered.TerritoryID
    

    This one has an inner join and subquery containing your filtered results. Untested but should work.