>Ok, I'm stuck. Have two tables parent..child one to many. need to crate a view that has a number of filters on the parent. The child table is a set of timestamped entries. need to create a view of select parent(s)..one lastest timestamped child (not the entire child set). Have tried
>
>SELECT Products.*, Costing.*;
> FROM pdsdata!products INNER JOIN pdsdata!costing ;
> ON Products.prod_cid = Costing.prod_cid;
> WHERE Products.item_stat IN ("Current Line","Prod Development");
> AND Products.year = ?vp_year;
> AND Products.season = ?vp_season;
> GROUP BY Products.item_code;
> ORDER BY Costing.datetime DESC
>
>Sometimes I get the last (most recent) datetime costing entry, other parent not. I'm not sure how to proceed with constraining the select to only the one desired costing child row.
>Ideas
>TIA

SELECT Products.*, Costing.*;
FROM pdsdata!products INNER JOIN pdsdata!costing ;
ON Products.prod_cid = Costing.prod_cid;
WHERE Products.item_stat IN ("Current Line","Prod Development");
AND Products.year = ?vp_year;
AND Products.season = ?vp_season;
AND Costing.datetime IN ;
(SELECT MAX(datetime) FROM costing c WHERE prod_cid = products.prod_cid)

or something like that. I'm not in a position to actually try it right now.

HTH

-Mike