CODEWITHSUNDEEP
sqlgoogle-bigquery
Nik S
Nik S

Reputation: 105

Issue with IfNull using a join | Big Query

I need some help. I've got three tables that I need information from. The most important parameter is the DealID from my table Flostream.orders. If this is null, I want it replaced with the Mobileheads.survey.sales_rule which is the same format.

I've constructed this:

SELECT
    filename,
    IFNULL(dealID,mobileheads.surveys.sales_rule) AS DealIDcombo,
    COUNT(*) AS Total,
    SUM(integer(weight)) AS TotalWeight,
    SUM(Productweight)/1000 AS SumWeight,
    Currency,
    Deliverybasecost,
    ROUND(SUM(Deliverybasecost),2) AS TotalDelCost,
    Productsku,
    Productname,
    Dealstartdate
FROM
    [flostream.orders] 
    LEFT OUTER JOIN flostream.briisk 
        ON dealID = Uniquereference
    LEFT OUTER JOIN mobileheads.surveys
        ON mobileheads.surveys.order_number = ExternalReference
GROUP BY 
    filename,
    DealIDCombo,
    currency,
    Deliverybasecost,
    Productname,
    Productsku,
    dealstartdate
ORDER BY
    filename,
    Total desc;

My issue is with this:

LEFT outer JOIN flostream.briisk ON dealID = Uniquereference

Ideally I would like it to be:

LEFT outer JOIN flostream.briisk ON dealIDCombo = Uniquereference

but unfortunately that doesn't work.

Any ideas on how to tackle this?

Upvotes: 0

Views: 1017

Answers (1)

encc
encc

Reputation: 1633

This is because the join can't access fields that are computed after the join.

See how Ifnull uses the joined table. You need to nest these tables.

First the join with mobileheads.surveys and then the next join.

SELECT * FROM(
  SELECT
    filename,
    IFNULL(dealID,mobileheads.surveys.sales_rule) AS DealIDcombo,
    COUNT(*) AS Total,
    SUM(integer(weight)) AS TotalWeight,
    SUM(Productweight)/1000 AS SumWeight,
    Currency,
    Deliverybasecost,
    ROUND(SUM(Deliverybasecost),2) AS TotalDelCost,
    Productsku,
    Productname,
    Dealstartdate
  FROM
    [flostream.orders]
  LEFT OUTER JOIN mobileheads.surveys
      ON mobileheads.surveys.order_number = ExternalReference) as first
LEFT OUTER JOIN flostream.briisk
    ON first.dealIDCombo = Uniquereference
GROUP BY 
  filename,
  DealIDCombo,
  currency,
  Deliverybasecost,
  Productname,
  Productsku,
  dealstartdate
ORDER BY
   filename,
   Total desc;

Excuse the mess, i don't know where these field belong to. Hopefully this helps. Ask me if you need more explanation!

Upvotes: 1

Related Questions