Q:

How do I do a LIKE function referencing another Column?

So my problem is, I three tables that I'm working with, Table A/Table B/Table C, Table A & Table B both have number mechanisms within them. So lets say Table A is Spend, Table B is Revenue. Table C contains the name of the Video Game along with a secondary name it goes by. What I'm trying to do is be able to have the Video Game name and Video Game Name Secondary both show and add together.
I've tried to do something like,
a.Name LIKE c.Name_Secondary

But that only shows a.Name results. The Tables look like the following:
Table A
Name           |    Revenue
TW WH2         |    30.00
Battlefield    |    20.00
TW WH2         |    15.00
Battlefield    |     5.00
T2 DLC 18674   |     5.00
B DLC 57948    |    10.00
T2 DLC 18674   |     5.00
B DLC 57948    |     5.00

Table B
Name           |    Spend
TW WH2         |    60.00
Battlefield    |    40.00
TW WH2         |    40.00
Battlefield    |    20.00
T2 DLC 18674   |     9.00
B DLC 57948    |    15.00
T2 DLC 18674   |    10.00
B DLC 57948    |     9.00    

Table C        
Name           |    Name_Secondary
TW WH2         |    T2 DLC 18674
Battlefield    |    B DLC 57948

The current Query that I have looks like:
SELECT DISTINCT
  a.Name,
  SUM(b.Spend),
  SUM(a.Revenue)
FROM
  Example.Table_A a

INNER JOIN
  (
    SELECT
      Name,
      SUM(Spend) AS Spend
    FROM
      Example.Table_B
    GROUP BY 1) b
ON
 a.Name = b.Name

INNER JOIN
  Example.Table_C c
ON
 a.Name = b.Name

GROUP BY
  1

What I have tried is adding in Name_Secondary into the first SELECT,
SELECT DISTINCT
  a.Name,
  c.Name_Secondary
  SUM(b.Spend),
  SUM(a.Revenue)
FROM
  Example.Table_A a

But the results I got were only from a.Name, the results I got are as follows:
Name         |      Name_Secondary    |    Spend     |    Revenue
TW WH2       |      T2 DLC 18674      |   100.00     |     45.00
Battlefield  |      B DLC 57948       |    60.00     |     25.00

I've also tried adding it into a WHERE function, something like:
WHERE
 a.Name LIKE c.Name_Secondary
GROUP BY
  1

But the same result as above was displayed without the Name_Secondary column inserted.
What I would like the results to show is:
Name         |     Spend     |   Revenue
TW WH2       |     118.00    |    55.00
Battlefield  |      84.00    |    40.00

Is this possible to do? Or would it end up being something like:
WHERE
 (a.Name LIKE '%TW%'
OR a.Name LIKE '%Batt%'
OR a.Name LIKE '%T2_DLC_18674%'
OR a.Name LIKE '%B_DLC_57948%')
GROUP BY
  1

Any help would be great and if further clarification is needed I can certainly add more information as an Edit.
-Maykid
EDIT1
I should clarify this more, TableC is only a lookup table that has Names that contain a Name_Secondary only. So for TableA & TableC they will have hundreds of names while TableC will only have a few (20-30). For example:
Table A 
Name
TH WH2
Battlefield
TH WH
Satisfactory
Starcraft II

Table C
Name              |    Name_Secondary
TW WH2            |    T2 DLC 18674
Battlefield       |    B DLC 57948

As you can see for this, Table C only has 2 out of the 5 listed Names as only 2 have Name_Secondary. Sorry for the confusion and not clarifying it better!

A:

Below is for BigQuery Standard SQL     
#standardSQL
WITH flattened_tableC AS (
  SELECT Name, Name AS join_value FROM `project.dataset.tableC` c UNION ALL
  SELECT Name, Name_Secondary AS join_value FROM `project.dataset.tableC` c 
), revenues AS (
  SELECT c.Name, SUM(Revenue) AS Revenue
  FROM flattened_tableC c
  LEFT JOIN `project.dataset.tableA` a ON c.join_value = a.Name
  GROUP BY 1 
), spending AS (
  SELECT c.Name, SUM(Spend) AS Spend
  FROM flattened_tableC c
  LEFT JOIN `project.dataset.tableB` b ON c.join_value = b.Name
  GROUP BY 1 
)
SELECT Name, Spend, Revenue 
FROM spending 
FULL OUTER JOIN revenues 
USING (Name)  

If to apply to sample data from your question - above query produce below result   
Row Name        Spend   Revenue  
1   TW WH2      119.0   55.0     
2   Battlefield 84.0    40.0     

