-2

I want to combine the two tables below in Big Query using a full outer join. Table A does not have certain products that I need to bring over from table B, but when I join on campaign & subcampaign, my join is not bringing over the 'CellPhone' data. My results looks more like a left join. See below for my query

SELECT
a.campaign 
, a.subcampaign
, a.product
, sum(sales)
, sum(cost)
FROM
(
SELECT
campaign 
, subcampaign 
, product 
, sum(sales)
FROM
  table_a
GROUP BY
  1, 2, 3
) a
FULL OUTER JOIN
(
  SELECT
  campaign 
  , subcampaign
  , product
  , sum(cost)
  FROM
    table_b
  GROUP BY 1,2,3
) b
ON
  a.campaign = b.campaign
  AND a.subcampaign = b.subcampaign
GROUP BY
  1,2,3

Table a

Campaign Subcampaign Product Sales
Campaign 1 Store 581 Gaming $50
Campaign 1 Store 583 TV $100

Table b

Campaign Subcampaign Product Cost
Campaign 1 Store 581 Gaming $25
Campaign 1 Store 583 TV $75
Campaign 1 Store 584 Cellphone $10

Desired result:

Campaign Subcampaign Product Sales Cost
Campaign 1 Store 581 Gaming $50 $25
Campaign 1 Store 583 TV $100 $75
Campaign 1 Store 584 Cellphone NULL $10
philipxy
  • 14,867
  • 6
  • 39
  • 83
smoking_crab
  • 1
  • 1
  • Debug questions require a [mre]--cut & paste & runnable code including initialization; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. [ask] [Help] When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) – philipxy Nov 02 '22 at 03:08
  • 1
    Please include your full query. My best guess is that you have a WHERE clause, but why are you making us guess? Please include the actual results you get for the example data provided? Don't just describe it, actually show it. – MatBailie Nov 02 '22 at 03:18
  • That is not "a left join". A non-natural join returns the columns from the left table then the columns from the right table. PS When you don't give a [mre] we don't know what you did & when you don't "say what you expected & why, justified by documentation" you are essentially asking us to (re)write a textbook with bespoke tutorial with no details on what you misunderstand or do or don't understand. Basic questions are faqs. [research effort](https://meta.stackoverflow.com/q/261592/3404097) [“help me"](https://meta.stackoverflow.com/q/284236/3404097) – philipxy Nov 02 '22 at 03:20
  • What does "not bringing over the `'Cell Phone'` data" mean? Do you mean `'Cellphone'`? What is that data? And what does it mean to not bring it over? What does it mean to bring it over? Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. – philipxy Nov 02 '22 at 03:51
  • 1
    Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. – Community Nov 02 '22 at 09:15
  • I provided an answer I think will help you, but note that your provided queries and data do not even mention `CellPhone` which is what your question is ostensibly about. – EdmCoff Nov 02 '22 at 14:53
  • Any reason not using **product** as a join key..? *full outer join* with 3 join keys will make the result you expect without an aggregation in outer query. **SELECT * FROM table_a FULL JOIN table_b USING (campaign, subcampaign, product)** – Jaytiger Nov 02 '22 at 15:07
  • I even summarized the [mre] page in a comment, but you still haven't given one. You also didn't act on my other comments. PS Look at the results of the subqueries that are input to the joins, and look at SELECT * of the join. PS See [How do comment replies work?](https://meta.stackexchange.com/q/43019/266284) to learn to use `@x` to notify 1 non-sole non-poster commenter `x` per comment about that comment. Posters, sole commenters & followers of posts always get notified. Without `@` other commenters get no notification. – philipxy Nov 03 '22 at 01:06

2 Answers2

0

I think the problem is likely your select clause, not the join.

I suspect the confusion is that you are select a.campaign (etc.) even in cases where the join is not matching anything in table_a. If there is no match in table_a, a.campaign/a.subcampaign/a.product will all be null.

You probably want something more like the following in your outer query:

SELECT
 COALESCE(a.campaign, b.campaign)
 , COALESCE(a.subcampaign, b.subcampaign)
 , COALESCE(a.product, b.product)
 , sum(sales)
 , sum(cost)
[...]
GROUP BY
 COALESCE(a.campaign, b.campaign)
 , COALESCE(a.subcampaign, b.subcampaign)
 , COALESCE(a.product, b.product)

This way, if a.campaign (etc.) is null, it will fall back on b.campaign. This is safe, since we know that if both have values they must be equal.

EdmCoff
  • 3,506
  • 1
  • 9
  • 9
0

You are aggregating before joining the two tables, which is why you might not be getting any null values in the result. Try selecting all values from the join and then aggregating to get the result you want like below:

SELECT
  ab.campaign, ab.subcampaign, ab.product, SUM(sales), SUM(cost)
FROM (
  SELECT *
  FROM
    table_a
  FULL OUTER JOIN
    table_b
  ON
    a.campaign = b.campaign
    AND a.subcampaign = b.subcampaign ) ab
GROUP BY
  1,2,3
Shivani
  • 36
  • 7