dnasca

--INNER JOIN multi-table join example
SELECT
  p.Name AS ProductName,
  pc.Name AS CategoryName,
  ps.Name AS SubcategoryName

FROM
  Production.Product AS p
  
  INNER JOIN

dnasca

--which rows are in the production table that are not in the archive table?

SELECT
  bea.BusinessEntityId,
  bea.AddressId,
  bea.AddressTypeId,
  bea.rowguid,
  bea.ModifiedDate

FROM

dnasca

SELECT 
  sod.SalesOrderId,
  sod.SalesOrderDetailId,
  sod.ProductId,
  sod.OrderQty,
  so.SpecialOfferId,
  sod.ModifiedDate,
  so.StartDate,
  so.EndDate,
  so.Description

dnasca

SELECT
  e.BusinessEntityId,
  e.HireDate,
  e.ManagerId,
  m.HireDate

FROM
  HumanResources.Employee AS e
  
  LEFT OUTER JOIN

dnasca

--how many rows?
SELECT COUNT(*)
FROM HumanResources.Employee;

--what gets returned?
SELECT e.BusinessEntityId, edh.DepartmentId
FROM HumanResources.Employee AS e
CROSS JOIN HumanREsources.EmployeeDepartmentHistory AS edh;

--practical use?

dnasca

--Put the predicate that determines the join condition in the ON clause (ie. p.ProductId = od.ProductId)
--Put the predicate that specifies the outer row filter in the WHERE clause

--LEFT OUTER returns all rows from the left table that match the right table and also rows from the left table that do NOT match
SELECT 
  p.Name,
  od.SalesOrderDetailId,
  od.OrderQty

FROM

dnasca

--ansi sql-92 standard syntax (filter rows in the ON clause)

SELECT
  p.Name,
  od.ProductId,
  od.SalesOrderDetailId,
  od.OrderQty
  
FROM 
  Production.Product AS p

dnasca

-- returning the first 25 rows
SELECT
  e.FirstName,
  e.LastName,
  e.AddressLine1

FROM 
  HumanResources.vEmployee AS e
  
ORDER BY

dnasca

-- applying predicates to a group

SELECT 
	SOD.ProductID,
	SOD.SpecialOfferID,
	SUM(SOD.OrderQty) AS OrderQtyByProductId

FROM 
	Sales.SalesOrderDetail AS SOD

dnasca

$.each(parsedCsv, function (key, val) {
  key = key.toLowerCase().replace(/\s+/g, '');
  $('#pool').append('<li id="' + key + '"class="list-group-item">' + val + '</li>');
});