title: Create a view integrating previous views in SQL
body: <p>I created three views in my new database using data from AdventureWorks2014. All three views compile and save just fine, there are no problems with them. However, when I try to make one full view including all three, nothing seems to be working.</p>
  
  <p>Views <code>creation</code>, <code>creation2</code> and <code>creation3</code>:</p>
  
  <pre><code>ALTER VIEW [creation] AS
  SELECT sd.ProductID, YEAR(sh.OrderDate) AS 'Year',
   MONTH(sh.OrderDate)AS 'Month', DAY(sh.OrderDate) AS 'Day',
  sd.UnitPrice AS 'Value', sd.OrderQty AS 'Quantity', NULL AS 'col7',
  NULL AS 'col8'
  FROM AW2014.Sales.SalesOrderHeader AS sh
  INNER JOIN AW2014.Sales.SalesOrderDetail AS sd
  ON sd.SalesOrderID = sh.SalesOrderID
  INNER JOIN AW2014.Sales.Customer AS c
  ON c.CustomerID = sh.CustomerID
  INNER JOIN AW2014.Person.Person AS p
  ON p.BusinessEntityID = c.PersonID
  WHERE p.PersonType LIKE 'IN'
  
  ALTER VIEW [creation2]AS
  SELECT p.FirstName, p.MiddleName, p.LastName,
  CONCAT(a.AddressLine1, ' ',a.AddressLine2,', ',a.City) AS 'Address',
  cr.Name, NULL AS 'col6', NULL AS 'col7', NULL AS 'col8'
  FROM AW2014.Person.Person AS p
  INNER JOIN AW2014.Person.BusinessEntityAddress AS bea
  ON p.BusinessEntityID= bea.BusinessEntityID
  INNER JOIN AW2014.Person.Address AS a
  ON bea.AddressID = a.AddressID
  INNER JOIN AW2014.Person.StateProvince AS sp
  ON a.StateProvinceID = sp.StateProvinceID
  INNER JOIN AW2014.Person.CountryRegion AS cr
  ON sp.CountryRegionCode = cr.CountryRegionCode
  WHERE p.PersonType LIKE 'IN'
  
  ALTER VIEW [creation3] AS
  SELECT DISTINCT p1.ProductID, p1.Name AS 'Product', pc.Name AS 'Category', 
  sc.Name AS 'Subcategory',
  p1.StandardCost, p1.Color, p1.Weight, CONCAT(p1.SizeUnitMeasureCode,', ',p1.WeightUnitMeasureCode) AS 'Units'
  FROM AW2014.Production.Product AS p1
  INNER JOIN AW2014.Production.ProductSubcategory AS sc
  ON p1.ProductSubcategoryID = sc.ProductSubcategoryID
  INNER JOIN AW2014.Production.ProductCategory AS pc
  ON pc.ProductCategoryID = sc.ProductCategoryID
  INNER JOIN AW2014.Sales.SalesOrderDetail AS sd
  ON p1.ProductID = sd.ProductID
  INNER JOIN AW2014.Sales.SalesOrderHeader AS sh
  ON sd.SalesOrderID = sh.SalesOrderID
  INNER JOIN AW2014.Sales.Customer AS c
  ON sh.CustomerID = c.CustomerID
  INNER JOIN AW2014.Person.Person AS p
  ON c.PersonID = p.BusinessEntityID
  WHERE p.PersonType LIKE 'IN'
  </code></pre>
  
  <p>I made every view have the same amount of columns, as I've read on stack I should if I want to use <code>union</code> statement. I did it like this:</p>
  
  <pre><code>CREATE VIEW [a] AS
  SELECT * FROM [creation2]
  UNION ALL
  SELECT * FROM [creation]
  </code></pre>
  
  <p>and I get a message that my command was completed successfully. Yet when I try to <code>SELECT * FROM [a]</code>, it executes endlessly with no results. After checking <code>dbo.a</code> in the drop down menu, the only columns it has are the ones from the first view (in this instance, <code>creation2</code>). It's as if it ignores (or can't execute?) the <code>union</code> or <code>union all</code> statements.</p>
  
  <p>I tried using <code>JOIN</code> as well, adding a <code>p.PersonType</code> column to each of the views, and joining them on those. It worked just fine as long as I was only selecting them, but when I tried to put them into a new view, I was getting an error that a view should have distinct columns. I can't make it distinct if I want to join them all on it, can I? 
  Either way, my question stands - how can I put all three of my views into a new one, considering all these options above have failed me? Maybe you have some tips, maybe I made some dumb mistakes in my code? I used SQL before but years ago, and I'd love any tips on improvement as well. </p>
  
  <p>I'm using Microsoft SQLServer Management Studio 2017 btw. Thank you!</p>