Can you make it a subquery? select p.partnum, (select partnum, sum(quantity) as recqty......), cast(p.part........ G > To: [email protected] > Subject: Combining Queries > Date: Wed, 8 Feb 2012 14:00:23 -0500 > From: [email protected] > > > I have a page where I need to combine four queries into one query in order to > use the query to build a spreadsheet. > > I have successfully combined the first three queries and obtain the desired > results; however, when trying to incorporate the fourth and final query, I > cannot figure out how to do it. > > My query so far is: > > select p.partnum, cast(p.partdescription as varchar(100)) as partdescription, > sum(w.allocqty) as allocated, sum(w.onhandqty) as onhand, > count(j.jobnum) as jobcount, sum(j.requiredqty) as qtyNeeded, > sum(j.issuedqty) as qtysent > , count(d.ordernum) as ordercount, sum(d.orderqty) as ordered, > sum(s.ourinventoryshipqty + s.ourjobshipqty) as shipqty > > from part p with(nolock), [otherServer].internal.dbo.order_tracker t > with(nolock), partwhse w with(nolock), jobmtl j with(nolock) > , orderdtl d with(nolock) left join shipdtl s with(nolock) on (d.company = > s.company and d.partnum = s.partnum and d.ordernum = s.ordernum) > > where p.company = '85' > and w.company = p.company > and j.company = p.company > and d.company = p.company > and j.partnum = p.partnum > and w.partnum = p.partnum > and p.partnum = t.partnum > and d.partnum = p.partnum > > group by p.partnum, cast(p.partdescription as varchar(100)) > > > The query I am trying to incorporate is: > > select partnum, sum(quantity) as recqty > from partdtl with (nolock) > where PartDtl.Company = '85' > and PartDtl.Type = 'Mtl' > and PartDtl.Plant = 'MfgSys' > and PartDtl.RequirementFlag = 0 > and jobnum not like 'MRP%' > and PartDtl.partnum in (select partnum from > [otherServer].internal.dbo.order_tracker t with(nolock)) > group by partnum > > This is not my strong point, but feel I've made some progress up to now. Any > help or guidance is appreciated. > > >
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3442 Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/sql/unsubscribe.cfm
