>I'm trying to build a cursor that shows all values from the parent cursor, along with the lookup value of the most recent child record. I'm running into difficulties because not all parent records have child records.
>
>For this set of data, where [Small], [Medium], and [Large] are values from a lookup table.
>
Customer1 has 3 orders; Oct 1 for 5000 [Small], Nov 1 for 12000 [Large], Dec 1 for 7000 [Medium]
>Customer2 has 0 orders;
>Customer3 has 2 orders; Nov 1 for 7000 [Medium], Dec 1 for 11000 [Large]
>Customer4 has 1 orders; Oct 1 for 4000 [Small]
>Customer5 has 0 orders;
>
>The result set should look like:
>
Customer1 Dec 1 [Medium]
>Customer2 {//} []
>Customer3 Dec 1 [Large]
>Customer4 Oct 1 [Small]
>Customer5 {//} []>
>So far, my results have either wound up with 3 Customers(cutting out 2 and 5) or 8 Customers because of the duplicates. I know this shouldn't be that hard, but I'm not seeing what I missed.
select t1.customer, t2.date, t3.size ;
from customer t1 ;
left join orders t2 on t1.custID = t2.custID ;
and t2.date = (select max(date) from orders o1 where o1.cust_id = t2.custid ) ;
left join lookup t3 on t2.amount = t3.amountCetin
