SQL INNER JOIN Without Repeats

Question

Getting the next table:

• Column1 - OrderID - Earliest orders of customers from Column2
• Column2 - CustomerID - Customers from orders in Column1
• Column3 - OrderID - All *Other* orders of customers from Column2 which do not appear in Column1

This is my query and I'm looking for a way to apply the rules mentioned above:

SELECT O1.orderid, C1.customerid, O2.Orderid 
FROM orders AS O1 
INNER JOIN customers AS C1 ON O1.customerid = C1.customerid
RIGHT JOIN orders AS O2 ON C1.customerid = O2.customerid
WHERE    O1.orderdate >= '2014-01-01' 
AND      O1.orderdate <= '2014-03-31' 
ORDER BY O1.orderid

Thanks in advance

Answer 1

Not entirely sure why you want to get a result out like this as the earliest order will repeat for each order for the given customer.

SELECT earliestOrders.orderid, C1.customerid, O1.Orderid 
FROM orders AS O1 
INNER JOIN customers AS C1 ON O1.customerid = C1.customerid
INNER JOIN (
    select o.customerid, min(o.OrderId) as OrderId
    from orders o
    Group by o.customerid
    ) earliestOrders
ON earliestOrders.CustomerId = C1.CustomerId
AND earliestOrders.orderid <> O1.Orderid