Nested Query

[ddtpmyra]

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.

[U]Table_Customer[/U]
Name            Address 
customer 1  454 B St. Brooklyn
customer 2  454 Hyde St. San Francisco
customer 3  445 Way Ln Michigan

[U]table_history_order[/U]
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

[yarbrough40]

Use a subquery.
try this:

SELECT Table_Customer.[Name],th.date_order,CASE WHEN th.name_count = 1 THEN 'Y' ELSE 'N' END AS first_order 
FROM  Table_Customer
LEFT JOIN(SELECT [Name],  MAX(date_order) as date_order, COUNT([NAME]) as name_count 
FROM table_history_order GROUP BY [Name])th
ON th.[Name] = Table_Customer.Name

[NeoPa]

Why is the data from line #9 not reflected in your output? I would have expected to see that with a Y for [First_Order].

[NeoPa]

I would expect something like the following should work assuming the question should have been what I think it should :

SELECT   tHO1.Name
       , tHO1.Item_Order
       , tHO1.Qty
       , tHO1.Date_Order
       , Max(CASE WHEN tHO2.Name Is Null THEN 'Y' ELSE 'N' END) AS [First_Order]
FROM     [History_Order] AS [tHO1]
         LEFT JOIN
         [History_Order] AS [tHO2]
  ON     (tHO1.Name = tHO2.Name)
 AND     (tHO1.Date_Order > tHO2.Date_Order)
GROUP BY tHO1.Name
       , tHO1.Item_Order
       , tHO1.Qty
       , tHO1.Date_Order