SQL : join 3 tables and show all data from the 2 tables in sub query

Question

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.

Listed below is an example of the 3 tables I am using.

"FileTable":

---------------------
|FileTable          |
---------------------
| Id | Name         |
---------------------
| 1  |  File1.docx  |
| 2  |  File2.xlsx  |
| 3  |  File3.pdf   |
---------------------

"TagTable":

---------------
|TagTable     |
---------------
| Id | Name   |
---------------
| 1  |  Tag1  |
| 2  |  Tag2  |
| 3  |  Tag3  |
| 4  |  Tag4  |
| 5  |  Tag5  |
| 6  |  Tag6  |
---------------

"TagValueTable":

------------------------------------
|TagValueTable                     |
------------------------------------
|FileId | TagId | Value            |
------------------------------------
| 1     |  1    |  file1tag1value  |
| 1     |  2    |  file1tag2value  |
| 1     |  4    |  file1tag4value  |
| 2     |  1    |  file2tag1value  |
| 2     |  4    |  file2tag4value  |
------------------------------------

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.

I have written the following query but it only shows files and tags if there is a value in the "TagValue" table.

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

I would like for the result to show up like below.

----------------------------------------------------------
|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               |
----------------------------------------------------------

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.

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.

Any help is appreciated...

Thanks,

Robert

squillman · Accepted Answer · 2018-06-19T21:05:45.903

You can use a CROSS JOIN and an OUTER JOIN here instead of an INNER JOIN.

SELECT 
    f.Id as FileID,
    f.Name AS FileName,
    t.Name AS TagName,
    tv.Value AS TagValue
FROM FileTable f
CROSS JOIN TagTable t
LEFT JOIN TagValueTable tv ON tv.FileId = f.Id
                           AND tv.TagId = t.Id

The CROSS JOIN will get you all permutations of FileTable and TagTable. The LEFT JOIN will bring in the tag values from the TagValueTable while keeping all of the results from the previous JOIN. An INNER JOIN, such as what you are using, will return only the records that match from both tables.

Here's a question that talks about the difference between an INNER and OUTER join.
What is the difference between "INNER JOIN" and "OUTER JOIN"?

score 0 · Answer 2 · answered Jun 19 '18 at 21:03

0

select x.FileId, x.FileName, x.TagName, tv.Value
from (
    select f.id FileId, f.name FileName, t.id TagId, t.Name TagName
    from FileTable f, TagTable t
    ) x
    left join TagValueTable tv on x.FileId = tv.FileId and x.TagId = tv.TagId

answered Jun 19 '18 at 21:03

Skippy

1,595
1
9
13

This does work, but you should discontinue using the deprecated implied INNER JOIN syntax. – squillman Jun 19 '18 at 21:06
@squillman I wouldn't call it implied INNER JOIN because in fact as you know when you omit the WHERE part it's a CROSS JOIN. But still, I agree to discontinue the use of that syntax. – Kamil Gosciminski Jun 19 '18 at 21:17
Yes, I agree, although actually it's an implied CROSS JOIN because there's no WHERE clause. – Skippy Jun 19 '18 at 21:18

score 0 · Answer 3 · answered Jun 19 '18 at 21:33

Since you want each of the Tags for the each file, you'll need to CROSS APPLY the Tags and then LEFT OUTER JOIN your link table to get the Value associated with the file and tag.

SETUP

CREATE TABLE FileTable ( Id int, Name varchar(20) ) ;

INSERT INTO FileTable (Id, Name)
VALUES ( 1, 'File1.docx' ), ( 2, 'File2.xlsx' ), ( 3, 'File3.pdf' ) ;

CREATE TABLE TagTable ( Id int, Name varchar(20) ) ;

INSERT INTO TagTable (Id, Name)
VALUES 
    ( 1, 'Tag1' )
  , ( 2, 'Tag2' )
  , ( 3, 'Tag3' )
  , ( 4, 'Tag4' )
  , ( 5, 'Tag5' )
  , ( 6, 'Tag6' )
;

CREATE TABLE TagValueTable ( FileId int, TagId int, theValue varchar(20) );

INSERT INTO TagValueTable (FileId, TagId, theValue)
VALUES 
    (1,1,'file1tag1value')
  , (1,2,'file1tag2value')
  , (1,4,'file1tag4value')
  , (2,1,'file2tag1value')
  , (2,4,'file2tag4value')
;

QUERY:

SELECT ft.Name, ca.TagName, tvt.theValue 
FROM FileTable ft
CROSS APPLY (
  SELECT tt.ID AS TagID, tt.Name AS TagName
  FROM TagTable tt
) ca
LEFT OUTER JOIN TagValueTable tvt ON ft.ID = tvt.FileID
    AND ca.TagID = tvt.TagID
GO

Name       | TagName | theValue      
:--------- | :------ | :-------------
File1.docx | Tag1    | file1tag1value
File1.docx | Tag2    | file1tag2value
File1.docx | Tag3    | null          
File1.docx | Tag4    | file1tag4value
File1.docx | Tag5    | null          
File1.docx | Tag6    | null          
File2.xlsx | Tag1    | file2tag1value
File2.xlsx | Tag2    | null          
File2.xlsx | Tag3    | null          
File2.xlsx | Tag4    | file2tag4value
File2.xlsx | Tag5    | null          
File2.xlsx | Tag6    | null          
File3.pdf  | Tag1    | null          
File3.pdf  | Tag2    | null          
File3.pdf  | Tag3    | null          
File3.pdf  | Tag4    | null          
File3.pdf  | Tag5    | null          
File3.pdf  | Tag6    | null

db<>fiddle here

Just saw that @squillman beat me to the same answer while I was testing mine out. His is a bit cleaner with the `CROSS JOIN` instead of the `CROSS APPLY`. They're pretty much doing the same thing though. — Shawn, Jun 19 '18 at 21:36
NOTE: They _ARE_ slightly different in how they operate. Depending on your data, one may perform better than the other. — Shawn, Jun 19 '18 at 21:40
Thanks I will do some testing to see which way has the best performance for our situation. This helped a lot!! — Robert, Jun 20 '18 at 00:31

SQL : join 3 tables and show all data from the 2 tables in sub query

3 Answers3