# Issue

*This Content is from Stack Overflow. Question asked by SwimJim *

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
```

# Solution

```
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
```

This Question was asked in StackOverflow by SwimJim and Answered by Samuel It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.