I've got a full join that isn't working like how I expected it to.
I'm joining on 4 columns. However, one of the columns has some blank values, causing some numbers to come up[ unjoint. When I take this column out, the query works. However, I have to have this certain column for the report as it's how we are reporting on the numbers.
Query in question: (the column that doesn't work is the OM.line_item_id)
SELECT DBM.dated, DBM.line_item, DBM.line_item_id, DBM.insertion_order, DBM.insertion_order_id, DBM.device_type, DBM.market, DBM.impressions, DBM.clicks, DBM.amount_spent_EUR, OM.orders, OM.revenue, OM.device FROM ( SELECT DATE_FORMAT(DATE_PARSE(date,'%Y/%m/%d'),'%Y-%m-%d') AS dated, line_item, line_item_id, insertion_order, insertion_order_id, device_type, trim(SPLIT_PART(insertion_order,'|',3)) AS market, cast(impressions as double) as impressions, cast(clicks as double) as clicks, CAST(media_cost_advertiser_currency AS DOUBLE)*1.15 AS amount_spent_EUR FROM ralph_lauren_google_sheet_dbm_data WHERE dated >= '2019-03-31' AND dated <= {{days_ago 1}} GROUP BY 1,2,3,4,5,6,7,8,9,10 )DBM FULL outer JOIN ( SELECT dated, line_item_id, device, market, sum(orders) as orders, sum(revenue)+sum(shipping_revenue)-sum(coupon_discount) as revenue FROM ( select dated, utm_content_v21 as line_item_id, order_currency_code_v33_evar33, case lower(mobile_device_type) when 'other' then 'Desktop' when 'tablet' then 'Tablet' when 'mobile phone' then 'Smart Phone' else 'Other' End as device, case geosegmentation_countries when 'united kingdom' then 'UK' when 'germany' then 'DE' when 'france' then 'FR' when 'italy' then 'IT' when 'spain' then 'ES' else 'other' end as market, sum(cast(orders as bigint))as orders, case WHEN lower(order_currency_code_v33_evar33) LIKE '%gbp%' THEN sum(TRY_CAST(revenue AS DOUBLE)*1.15) ELSE Sum(TRY_CAST(revenue AS DOUBLE)*1) END as revenue, CASE WHEN lower(order_currency_code_v33_evar33) LIKE '%gbp%' THEN sum(TRY_CAST(order_level_shipping_revenue_e62_event62 AS DOUBLE)*1.15) ELSE sum(TRY_CAST(order_level_shipping_revenue_e62_event62 AS DOUBLE)*1) END as shipping_revenue, CASE WHEN lower(order_currency_code_v33_evar33) LIKE '%gbp%' THEN sum(TRY_CAST(order_level_coupon_discount_e77_event77 AS DOUBLE)*1.15) ELSE sum(TRY_CAST(order_level_coupon_discount_e77_event77 AS DOUBLE)*1) END as coupon_discount from ralph_lauren_ftp_all_eu_markets_ltc WHERE dated >= '2019-03-31' AND dated <= {{days_ago 1}} and last_touch_channel like 'Retargeting' and lower(utm_medium_v21) not like '%fbig%' and cast(orders as bigint) > 0 group by 1,2,3,4,5 ) GROUP BY 1,2,3 ,4-- revenue numbers are getting duplicated for some reason )OM ON DBM.dated = OM.dated AND DBM.line_item_id = OM.line_item_id and DBM.device_type = OM.device AND DBM.market = OM.market wouldn't a full outer join allow me to join up numbers if they have the other three columns being joint up?
thanks
3Related questions 3 SQL Full Outer Join 0 Adding new JOIN to Presta's queries 0 Presto/mysql Self Join Table Related questions 3 SQL Full Outer Join 0 Adding new JOIN to Presta's queries 0 Presto/mysql Self Join Table 0 How to Left Join in Presto SQL? 0 sql presto query to join 2 tables interatably 0 How to fix full outer join of two tables when not all values exist in both tables 4 presto sql: multiple join with `using` statement 0 Joining two tables on a third table 1 Difference between JOIN expressions in Presto SQL 0 (Presto) SQL cross join two tables but only for a specific right column Load 7 more related questions Show fewer related questions
Reset to default
