join two dataset on two columns sql domo

user095063

I am using MySQL  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
```
And the other table represents 2020 Revenue
```
Week              Name              2020 Revenue
1              Paul                           554
1              Nick                           200
2              Sam                           400
```
I want the output be:
```
Week        Name              2019 Revenue              2020 Revenue
1             Paul              576356                                        554
1              Nick              246564                                      200
2              Sam            426547265                                 400
```

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

Cartergan

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

Hi, thank you for your reply. I have tried that as well, but it only returns 2019 Rev. I want two columns (2019 Rev & 2020 Rev) to be side by side, for comparison reason. Now if any Advertiser is not in 2019 or 2020 then let it be 0. I can't figure out the syntax for that

GrantSmith

@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

Cartergan

Do you have a table for the advirtiser information? If so, you can start with that table and join in the two years data

SELECT 
A.Advirtiser, 
B.Week, 
B.2019 Revenue,
C.2020 Revenue 
FROM AdvirtiserInformation A
LEFT JOIN 2019 B
ON A.Advirtiser = B.Advirtiser
LEFT JOIN 2020 C 
ON A.Advirtiser = C.Advirtiser
AND B.Week = C.Week 

jaeW_at_Onyx

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

 

GrantSmith

@jaeW_at_Onyx I recommended this method in case there were any advertisers which didn't have any revenue in 2019 or 2020 whereas the LEFT + RIGHT JOIN union method would only include advertisers which had revenue in 2019 or 2020.

 

Your method is preferred if we're only looking for advertisers with revenue but from what I gathered the initial ask was to include advertisers if they didn't have any revenue in either 2019 or 2020.

 

EDIT: Coming back to this there's two separate issues. What I was addressing was the possibility for advertisers with no revenue in 2019 or 2020 to appear in the dataset. What Jae is referring to is called Tidy data (concept created by Hadley Wickham of R fame. Read his paper here: http://vita.had.co.nz/papers/tidy-data.pdf) which is a better way of storing data to make analysis easier.