I am using MySQL with DOMO. I have two tables that I want to join on both columns. My data looks like the following:
The first table represents 2019 Revenue
Week Name 2019 Revenue
1 Paul 576356
1 Nick 246564
2 Sam 426547265
2 Frank 5436
And the other table represents 2020 Revenue
Week Name 2020 Revenue
1 Paul 554
1 Nick 200
2 Sam 400
2 Frank 500
I want the output be:
Week Name 2019 Revenue 2020 Revenue
1 Paul 576356 554
1 Nick 246564 200
2 Sam 426547265 400
2 Frank 5436 500
I have tried the following:
SELECT
`Week`,
`Advertiser`,
`2019 Revenue`
from `2019` as a
left join `2020` as b
on a.`Week` = b.`Week`
and a.`Advertiser` = b.`Advertiser`
Error message: SQL constraint violated: Column 'Week' in field list is ambiguous
The answer should be obvious. But the solution is that you get into the habit of always qualifying column names in queries:
SELECT a.Week, a.Advertiser, a.`2019 Revenue`, b.`2020 Revenue`
from `2019` a left join
`2020` b
on a.Week = b.Week and
a.Advertiser = b.Advertiser;