CODEWITHSUNDEEP
sqlgoogle-cloud-platformgoogle-bigquery
SwimJim
SwimJim

Reputation: 109

Bigquery Join syntax errors with example code that works in standard SQL

I am using big query and looking to find the toalAfterRefund value of the first order of an emailAddress. Both these select statements work as individual statements, but I'm having trouble joining them. I get the error

Syntax error: Expected end of input but got keyword JOIN at [1:1]

I'm having trouble understanding what I need to do to join these as these statements together in bigquery. I am trying to get the toalAfterRefund value of the emailAddresses first orderDate.

SELECT  H.emailAddress, H.orderId, H.orderDate, H.totalAfterRefund
           FROM  `nexgen-362616.orders.allOrders` AS H
          WHERE   totalAfterRefund>0 
          
JOIN
          (SELECT emailAddress, MIN(OrderDate) AS first_order
          FROM  `nexgen-362616.orders.allOrders` as X
          WHERE   totalAfterRefund>0 
          GROUP BY emailAddress) 
          ON H.emailAddress = X.emailAddress AND H.orderDate = X.first_order

Upvotes: 0

Views: 81

Answers (2)

nbk
nbk

Reputation: 49373

You have multiple problem in our code,

  1. WHERE clause belongs always at the end
  2. every subquery must have its own name

so

SELECT  H.emailAddress, H.orderId, H.orderDate, H.totalAfterRefund, X.first_order
           FROM  `nexgen-362616.orders.allOrders` AS H          
JOIN
          (SELECT emailAddress, MIN(OrderDate) AS first_order
          FROM  `nexgen-362616.orders.allOrders`
          WHERE   totalAfterRefund>0 
          GROUP BY emailAddress)  as X
          ON H.emailAddress = X.emailAddress AND H.orderDate = X.first_order
WHERE   totalAfterRefund>0

Upvotes: 0

Samuel
Samuel

Reputation: 3528

WITH table_emailAddress as
(SELECT emailAddress, MIN(OrderDate) AS first_order
          FROM  `nexgen-362616.orders.allOrders` as X
          WHERE   totalAfterRefund>0 
          GROUP BY emailAddress) 

SELECT  H.emailAddress, H.orderId, H.orderDate, H.totalAfterRefund
           FROM  `nexgen-362616.orders.allOrders` AS H
       
          
JOIN table_emailAddress  as X
     
          ON H.emailAddress = X.emailAddress AND H.orderDate = X.first_order

   WHERE   totalAfterRefund>0

Upvotes: 1

Related Questions