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

Please communicate your question properly & with sample data & desired output. — AK47, Apr 08 '14 at 09:48
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 What is mean by that? — Sivaraman, Apr 08 '14 at 09:49
#Sivaraman - I mean that orders that appear in Col 1 should not appear in Col 3 again. — Zephram, Apr 08 '14 at 10:02

sarin · Accepted Answer · 2014-04-08T10:11:58.400

0

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

edited Apr 08 '14 at 10:11

answered Apr 08 '14 at 09:52

sarin

5,227
3
34
63

I need the earliest order (from col1) not to repeat (in col3) How do I remove from Col3 all orders that exist in Col 1 ? – Zephram Apr 08 '14 at 09:59
i've just added an extra bit onto the join clause. try that – sarin Apr 08 '14 at 10:12
Thanks! That's exactly the piece of information I needed: You can add an "AND" clause after "ON". I didn't know that. – Zephram Apr 08 '14 at 10:38

score 0 · Answer 2 · answered Apr 08 '14 at 11:01

To find the first order per customer, look for first order dates per customer and then pick the one or one of the orders made by the customer then. (If orderdate really is just a date one customer can have placed more than one order that day, so we pick one of them. With MIN(orderid) we are likely to get the first one of that bunch :-)

Outer join the other orders and you are done.

If your dbms supports IN clauses on tuples, you get a quite readable statement:

select first_order.orderid, first_order.customerid, later_order.orderid
from
(
  select customerid, min(first_order.orderid) as first_orderid
  from orders 
  where (customerid, orderdate) in
  (
    select customerid, min(orderdate)
    from orders
    group by cutomerid
  )
) first_order
left join orders later_order 
  on later_order.customerid = first_order.customerid
  and later_order.orderid <> first_order.orderid
;

If your dbms doesn't support IN clauses on tuples, the statement looks a bit more clumsy:

select first_order.orderid, first_order.customerid, later_order.orderid
from
(
  select first_orders.customerid, min(first_orders.orderid) as orderid
  from orders first_orders
  inner join
  (
    select customerid, min(orderdate)
    from orders
    group by cutomerid
  ) first_order_dates
    on first_order_dates.customerid = first_orders.customerid
    and first_order_dates.orderdate = first_orders.orderdate
  group by first_orders.customerid
) first_order
left join orders later_order 
  on later_order.customerid = first_order.customerid
  and later_order.orderid <> first_order.orderid
;

SQL INNER JOIN Without Repeats

2 Answers2