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

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

CodePudding user response：

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

CodePudding user response：

You have multiple problem in our code,

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

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