return N records from table with 1 to many join
Posted on 2008-01-28
Here is my scenero. I have two tables the first is called orderdetail, it has columns such as modelnum, qtyallocated and more... The second table is called serialtrack, it has columns such as modelnum, serialnum etc. The orderdetail table can have a modelnum listed on multiple lines with a qtyallocated for each line (modelnum). The serialtrack table will have lots (500k or more) of serialnum's for each modelnum where orderdetail.modelnum = serialtrack.modelnum. What I need to do is to return only the SUM of orderdetail.qtyallocated(N) number of serialnum's from serialtrack table. The only link from orderdetail to serialtrack is the modelnum. The other requirement is that a field called state in serialtrack is = 1(available). i am sure this is not a hard as it seems to me because I'm not very experienced in SQL.