join two dataset on two columns sql domo

Since there are column names that are used in both datasets, you must specify where they come from. 

For example:

SELECT
A.`Week`,
A.`Advertiser`,
A.`2019 Revenue`
from `2019` as a
left join `2020` as b
on a.`Week` = b.`Week`
and a.`Advertiser` = b.`Advertiser`

Since you're joining on the week it will be the same data, you just need to specify the dataset since it doesn't automatically choose one if there are duplicates. 

@user095063 

A quick version would be 

SELECT
a.`Week`,
a.`Advertiser`,
a.`2019 Revenue`
b.`2020 Revenue`
from `2019` as a
left join `2020` as b
on a.`Week` = b.`Week`
and a.`Advertiser` = b.`Advertiser`

This requires an advertiser to have appeared in 2019 first to be displayed in your final table. If you want to make sure you include advertisers which you know of but haven't had any revenue you'd need to pull from an advertisers table (if you have one) which lists all of the known advertisers and create a cross product of all of the weeks and advertisers. This requires two separate steps.

The first being a transform in the MySQL data flow (I called it advertiser_weeks):

select distinct `Week`, a.`Advertiser`
FROM `test_2019_revenue` r, `test_advertisers` a

Output Dataset:

SELECT
x.`Week`,
x.`Advertiser`,
IFNULL(a.`Revenue`, 0) as "2019 Revenue",
IFNULL(b.`Revenue`, 0) as "2020 Revenue"
from `advertiser_weeks` as x
left join `test_2019_revenue` as a on x.`Advertiser` = a.`Advertiser` and x.`Week` = a.`Week`
left join `test_2020_revenue` as b
on x.`Week` = b.`Week`
and x.`Advertiser` = b.`Advertiser`

This will display all weeks and all advertisers regardless if they had any revenue or not in your tables.

If you don't have an advertisers table you can make one based off the 2019 and 2020 data as a transform table BEFORE the advertisers_week transform and just call it Advertisers:

select distinct `Advertiser`
from `2019`

UNION

select distinct `Advertiser`
from `2020`

Using this show advertisers which have had some revenue in either 2019 or 2020

@GrantSmith  ,

It seems like you're recommending this complex flow b/c MySQL doesn't support an OUTER JOIN.

You can emulate an OUTER JOIN by UNION'ing a LEFT and a RIGHT JOIN.  (remember that a UNION will remove duplicates

SELECT
a.`Week`,
a.`Advertiser`,
a.`2019 Revenue`
b.`2020 Revenue`
from `2019` as a
left join `2020` as b
on a.`Week` = b.`Week`
and a.`Advertiser` = b.`Advertiser`

UNION
SELECT 
b.`Week`, 
b.`Advertiser`, 
a.`2019 Revenue` 
b.`2020 Revenue` 
FROM `2019` as a 
RIGHT JOIN `2020` as b 
ON a.`Week` = b.`Week` and a.`Advertiser` = b.`Advertiser`

MORE IMPORTANTLY

I don't think you should get in the habit of JOIN'ing Data like this.  Instead, I'd recommend that you consider Stacking the data.

SELECT
`Week`,
`Advertiser`, 
'2019 as Year,
`2019 Revenue` as 'Revenue'
FROM tableA

UNION ALL

SELECT
`Week`,
`Advertiser`, 
'2020 as Year,
`2020 Revenue` as 'Revenue'
FROM tableB

Getting into the mindset of UNION'ing data will give you much more flexibility as you move foward b/c

1) if you decide to add another metric like '2021 data' you don't have to create an overly complex JOIN.

2) if you decide to add more granular metrics like 'QTD or MTD data, you don't have overly complex JOINs AND avoid restating data or blowing out metrics.

3) JOINS will become increasingly slower whereas UNION ALL will always be the faster table combining method because there is no additional processing step required when UNION ALL (as opposed to UNION which has to test for uniqueness)

4) when you start combining data from multiple datasets if your default is to think in JOIN's you'll run into roadblocks when your data has different granularities.  Whereas if you do a UNION you don't have to worry about the granularity of your data, you just have to conform columns.

Hope that helps / makes sense.