title: Union Columns Vertically in SQL
body: <p>Assume these Tables:</p>
  
  <p><strong>Group</strong>: (Id, Title):  <code>{1,G1}, {2,G2}, {3,G3}, {4, G4}</code></p>
  
  <p><strong>Category</strong>: (Id, Title): <code>{1, Cat1}, {2, Cat2}, {3, Cat3}, {4, Cat4}</code></p>
  
  <p><strong>Product</strong>: (Id, GroupId, CategoryId, Name):</p>
  
  <pre><code>{1, 1, 1, G1C1P1},
  {2, 1, 2, G1C2P2},
  {3, 1, 2, G1C2P3},
  {4, 2, 2, G2C2P4},
  {5, 2, 2, G2C2P5},
  {6, 3, 1, G3C1P6},
  {7, 3, 3, G3C3P7}
  </code></pre>
  
  <p><strong>Dealer</strong>: (Id, Name):  <code>{1, 'Dealer1'}, {2, 'Dealer2'}, {3, 'Dealer3'}</code></p>
  
  <p><strong>ProductDealer</strong> (Id, ProductId (UK), DealerId, LastSale, Number):</p>
  
  <pre><code>{1, 1, 1, 5, '2012-12-10 12:34:31'}, 
  {2, 2, 2, 120, '2012-11-10 12:34:31'}, 
  {3, 5, 1, 75, '2012-12-02 12:34:31'}
  </code></pre>
  
  <p>So I going to create a full view of product, this is my first try:</p>
  
  <pre><code>SELECT 
      [PR].[Id],
      [PR].[Name],
      [PR].[GroupId],
      [GR].[Title] AS [Group],
      [PR].[CategoryId],
      [CA].[Title] AS [Category]
  
  FROM [dbo].[Product] AS [PR]
  INNER JOIN [dbo].[Group] AS [GR] ON [PR].[GroupId] = [GR].[Id]
  INNER JOIN [dbo].[Category] AS [CA] ON [PR].[CategoryId] = [CA].[Id]
  </code></pre>
  
  <p>Then I think to add <code>ProductDealer</code> columns to view, so I try this one:</p>
  
  <pre><code>SELECT 
      [PR].[Id],
      [PR].[Name],
      [PR].[GroupId],
      [GR].[Title] AS [Group],
      [PR].[CategoryId],
      [CA].[Title] AS [Category],
      ISNULL(
      (SELECT [PD].[Id] FROM [dbo].[ProductDealer] AS [PD] 
       WHERE [PD].[ProductId] = [PR].[Id]),
       CAST(-1 AS BIGINT)
      ) AS [ProductDealerId],
      ISNULL(
      (SELECT [DE].[Id] FROM [dbo].[Dealer] AS [DE]
      INNER JOIN [dbo].[ProductDealer] AS [PD] ON [DE].[Id] = [PD].[DealerId]
       WHERE [PD].[ProductId] = [PR].[Id]),
       CAST(-1 AS BIGINT)
      ) AS [DealerId],
       ISNULL(
      (SELECT [DE].[Name] FROM [dbo].[Dealer] AS [DE]
      INNER JOIN [dbo].[ProductDealer] AS [PD] ON [DE].[Id] = [PD].[DealerId]
       WHERE [PD].[ProductId] = [PR].[Id]),
       CAST('HaveNotDealer' AS NVARCHAR)
      ) AS [Dealer],
      ISNULL(
      (SELECT [PD].[LastSale] FROM [dbo].[ProductDealer] AS [PD] 
       WHERE [PD].[ProductId] = [PR].[Id]),
      CAST('0001-01-01 00:00:01' AS DATETIME2)
      ) AS [LastSale],
      ISNULL(
      (SELECT [PD].[Number] FROM [dbo].[ProductDealer] AS [PD] 
       WHERE [PD].[ProductId] = [PR].[Id]),
      CAST(0 AS BIGINT)
      ) AS [SaleNumber]
  
  FROM [dbo].[Product] AS [PR]
  INNER JOIN [dbo].[Group] AS [GR] ON [PR].[GroupId] = [GR].[Id]
  INNER JOIN [dbo].[Category] AS [CA] ON [PR].[CategoryId] = [CA].[Id]
  </code></pre>
  
  <p>As you see for each column in <code>ProductDealer</code> I write a full select query I don't know is there any better way to do this? something like union this columns to first view, what is your suggestion to best implement it?</p>
  
  <p><strong>Update</strong></p>
  
  <p>In <code>ProductDealer</code> Table, the <code>ProductId</code> is UK, So each Product could have one dealer or nothing, I want if the product have a dealer get <code>ProductDealer</code> Columns in view and if not get my default values like: (-1, 'HaveNotDealer').</p>