I have two tables that I need to join together, customers & orders, and I need to return a singular row per customer, featuring said customer's first order within the database, along with the email and order id associated with that customer & date.
So far I've been able to get this much:
select email_address, order_id, order_date
from orders o
join customers c on c.customer_id = o.customer_id
where c.customer_id not in
(
select customer_id
from orders
group by customer_id
having count(distinct customer_id) > 1
)
order by order_date, order_id;
which returns the three columns I need, emails, order id, and order date, but when I cross-reference to the original table containing the order_date column, it's simply pulling them row by row:
| email_address | order_id | order_date |
| ------------- | -------- | ---------- |
| [email protected] | 1 | 2018-03-28 |
| [email protected] | 2 | 2018-03-28 |
| [email protected] | 3 | 2018-03-29 |
If I remove the distinct from the count, I get a table without order id 1 entirely
The result that I want is along the lines of:
| email_address | order_id | order_date |
| ------------- | -------- | ---------- |
| [email protected] | 1 | 2018-03-28 |
| [email protected] | 2 | 2018-03-28 |
| [email protected] | 4 | 2018-03-30 |