Hello,
I dont know how to write this thought to a query statement but heres the idea.
I have list of orders of customer, now I want a flag that will tell me this is a first time order.
- Table_Customer
-
Name Address
-
customer 1 454 B St. Brooklyn
-
customer 2 454 Hyde St. San Francisco
-
customer 3 445 Way Ln Michigan
-
- table_history_order
-
Name item_order qty date_order
-
customer 1 short 250 01/02/2012
-
customer 1 shirt 250 01/03/2012
-
customer 2 pants 250 01/04/2012
-
-
From the table above my query result should be
-
Name date_order first_order
-
customer1 01/03/2012 N
-
customer2 01/04/2012 Y
I started my query like this but I dont know how to add the flag that will tell me that is a first or not firt customer's order.
- Select name, date_order,
-
first_order
-
Case
-
WHEN select count(*) from table_history_order =>2 'i dont know how to write the where condition that will link to the custmer name outside this nested query.
-
THEN first_order 'Y'
-
WHEN select count(*) from table_history_order <=1
-
THEN first_order 'N'
-
END as first_order
-
from cutomer c left join customer_order co on c.name = co.name
