title: SQL : join 3 tables and show all data from the 2 tables in sub query
body: <p>I am trying to join three tables and show all the relevant data from both tables listed in the sub-query to get enough data so that I can perform a pivot later.</p>
  
  <p>Listed below is an example of the 3 tables I am using.</p>
  
  <p>"FileTable":</p>
  
  <pre><code>---------------------
  |FileTable          |
  ---------------------
  | Id | Name         |
  ---------------------
  | 1  |  File1.docx  |
  | 2  |  File2.xlsx  |
  | 3  |  File3.pdf   |
  ---------------------
  </code></pre>
  
  <p>"TagTable": </p>
  
  <pre><code>---------------
  |TagTable     |
  ---------------
  | Id | Name   |
  ---------------
  | 1  |  Tag1  |
  | 2  |  Tag2  |
  | 3  |  Tag3  |
  | 4  |  Tag4  |
  | 5  |  Tag5  |
  | 6  |  Tag6  |
  ---------------
  </code></pre>
  
  <p>"TagValueTable": </p>
  
  <pre><code>------------------------------------
  |TagValueTable                     |
  ------------------------------------
  |FileId | TagId | Value            |
  ------------------------------------
  | 1     |  1    |  file1tag1value  |
  | 1     |  2    |  file1tag2value  |
  | 1     |  4    |  file1tag4value  |
  | 2     |  1    |  file2tag1value  |
  | 2     |  4    |  file2tag4value  |
  ------------------------------------
  </code></pre>
  
  <p>I am trying to write a query that will show me the Files, with ALL available Tags with the tag values for the files if they are provided.</p>
  
  <p>I have written the following query but it only shows files and tags if there is a value in the "TagValue" table.  </p>
  
  <pre><code>SELECT 
      f.Id AS 'FileId', f.[Name] AS 'FileName', 
      t.[Name] AS 'TagName',
      tv.[Value] AS 'TagValue'
  FROM 
      [FileTable] f
  JOIN 
      TagValueTable tv ON tv.FileId = ISNULL(f.Id, tv.FileId)
  JOIN 
      TagTable t ON t.Id = ISNULL(tv.TagId, t.Id)
  ORDER BY
      f.Id
  </code></pre>
  
  <p>I would like for the result to show up like below.</p>
  
  <pre><code>----------------------------------------------------------
  |FileId  |FileName      |TagName     |TagValue           |
  ----------------------------------------------------------
  |  1     |File1.doc     |Tag1        |file1tag1value     |
  |  1     |File1.doc     |Tag2        |file1tag2value     |
  |  1     |File1.doc     |Tag3        |NULL               |
  |  1     |File1.doc     |Tag4        |file1tag4vaule     |
  |  1     |File1.doc     |Tag5        |NULL               |
  |  1     |File1.doc     |Tag6        |NULL               |
  |  2     |File2.xls     |Tag1        |file2tag1value     |
  |  2     |File2.xls     |Tag2        |NULL               |
  |  2     |File2.xls     |Tag3        |NULL               |
  |  2     |File2.xls     |Tag4        |file2tag4vaule     |
  |  2     |File2.xls     |Tag5        |NULL               |
  |  2     |File2.xls     |Tag6        |NULL               |
  |  3     |File3.pdf     |Tag1        |NULL               |
  |  3     |File3.pdf     |Tag2        |NULL               |
  |  3     |File3.pdf     |Tag3        |NULL               |
  |  3     |File3.pdf     |Tag4        |NULL               |
  |  3     |File3.pdf     |Tag5        |NULL               |
  |  3     |File3.pdf     |Tag6        |NULL               |
  ----------------------------------------------------------
  </code></pre>
  
  <p>The query needs to show all available tags for all files and their tag values. If the file doesn't have a tag value it should just be null. I am sure there is probably a better design for this but I am having to work with existing data so I am limited in the changes I can make.</p>
  
  <p>I have tried rewriting the above query many different ways using different joins but I cannot seem to get it work. Also I am also doing this in a view and cannot use a stored procedure.</p>
  
  <p>Any help is appreciated...</p>
  
  <p>Thanks,</p>
  
  <p>Robert</p>