Dear All, In my following query

[tsquare1975]

Dear All,

In my following query I join tables of sales and description of itemno

SELECT TOP (100) PERCENT O.EXPDATE, O.LOCATION, M.SEGMENT2,M.SEGMENT3, O.[DESC],
SUM(O.QTYSHIPPED) AS QTY, SUM(O.UNITPRICE * O.QTYSHIPPED) AS AMT,
dbo.ICLOC.[DESC] AS Loc, M.OPTFLD5,icsegv.[desc] as seg3des, csopt.data as seg2des
FROM icsegv, csopt, dbEINVD AS O INNER JOIN dbo.ICITEM AS M ON O.ITEM = M.FMTITEMNO INNER JOIN dbo.ICLOC ON O.LOCATION = dbo.ICLOC.LOCATION
where ((segval=m.segment3 and icsegv.segment=3))
and (csopt.code =optfld5 and

= 'dept')
GROUP BY O.LOCATION, M.SEGMENT2, dbo.ICLOC.[DESC], O.[DESC], M.OPTFLD5,O.EXPDATE,M.SEGMENT3,icsegv.[desc], csopt.data ORDER BY O.EXPDATE

I want to display all associated value along with “AMT “ = 0, even if no record is found against an item in sale table “O” during specified tiem period. currently it doesn't display whole row if no sale is found. It must display 0 amt alongwity all descriptions.

Plz guide me the solution
thanks

 

[ettienne]

You need a LEFT OUTER join, not an INNER join.

 

[tsquare1975]

Dear all,

I as per ur advise i use left outer join ...but still can't get desired results. plz tell me wat's wrong with this query as it doesn't give me all items from table "m" with null values in table "O"

SELECT TOP (100) PERCENT m.SEGMENT2, isnull(SUM(o.UNITPRICE * o.QTYSHIPPED),0) AS AMT, m.OPTFLD5
from dbo.oeinvd as o left outer join dbo.icitem as m on m.fmtitemno=o.item
where
optfld5 in
('1301','1302','1303','1304','1305','1306','1307')
and (o.EXPDATE = '20120616')
GROUP BY m.SEGMENT2, m.OPTFLD5


Thanks

 

[Jay Converse]

Your join is backwards