Link to home
Create AccountLog in
Avatar of jdr0606
jdr0606Flag for United States of America

asked on

Select all rows in SQL2005 table even if no match in join

I have a select statement below that I need to return all items from the #tmpBR table and grouped by week even if there is not a matching join in the other tables.

This is the code I'm trying to use and even though the #tmpBR table has 37,400 rows I end up with less that 10,000 in the result set.

Ideas?


select iv2.primvndr
      , tmp.SKUItemNumber as 'SKUItemNumber'
      , '001' as 'SKUDestinationIdentifier'
      , max(iv3.vnditnum) as 'SKUSourceIdentifier'
      , '' as 'SKUSourceShipPoint'
      --, sop1.docdate as 'Date'
      , (case when datepart(dw,sop1.docdate)=1 then sop1.docdate
            else sop1.docdate+(1-(datepart(dw,sop1.docdate)))
            end) as 'Date'
      , max(sop1.docdate-(datepart(dw,sop1.docdate)-datepart(dw,sop1.docdate))+1) as 'Sanmple'
      , max(sop1.docdate) as '1'
      , max(datepart(dw,sop1.docdate)) as '2'
      , max(datepart(dw,sop1.docdate)-datepart(dw,sop1.docdate)) as '3'
      , max(sop1.docdate+(1-(datepart(dw,sop1.docdate)))) as '4'
      , sum(sop2.quantity) as 'Sales'
      , 0 as 'Lost Sales'      
      , 0 as 'Promotional Sales'      
      , '' as 'Customer ID'      
      , '' as 'Event Downturn'      
      , '' as 'DependentDemand'      
      , ' ' as 'LostProduction'
from #tmpBR tmp
right outer join sop10200_sop30300_union sop2 on sop2.itemnmbr=tmp.SKUItemNumber
join sop10100_sop30200_union sop1 on sop1.sopnumbe=sop2.sopnumbe
right outer join iv00102 iv2 on iv2.itemnmbr=tmp.SKUItemNumber
      and iv2.locncode='WAREHOUSE'
right outer join iv00101 iv1 on iv1.itemnmbr=tmp.SKUItemNumber
right outer join iv00103 iv3 on iv3.itemnmbr=tmp.SKUItemNumber
where
      iv1.itmclscd!='NON-IV'
      and iv1.itemtype !='2'
      and sop1.docdate>'2012-04-30'
      and sop2.soptype='2'
group by tmp.SKUItemNumber
      , iv2.primvndr
      , (case when datepart(dw,sop1.docdate)=1 then sop1.docdate
            else sop1.docdate+(1-(datepart(dw,sop1.docdate)))
            end)
order by tmp.SKUItemNumber
Avatar of deighton
deighton
Flag of United Kingdom of Great Britain and Northern Ireland image

select iv2.primvndr
      , tmp.SKUItemNumber as 'SKUItemNumber'
      , '001' as 'SKUDestinationIdentifier'
      , max(iv3.vnditnum) as 'SKUSourceIdentifier'
      , '' as 'SKUSourceShipPoint'
      --, sop1.docdate as 'Date'
      , (case when datepart(dw,sop1.docdate)=1 then sop1.docdate
            else sop1.docdate+(1-(datepart(dw,sop1.docdate)))
            end) as 'Date'
      , max(sop1.docdate-(datepart(dw,sop1.docdate)-datepart(dw,sop1.docdate))+1) as 'Sanmple'
      , max(sop1.docdate) as '1'
      , max(datepart(dw,sop1.docdate)) as '2'
      , max(datepart(dw,sop1.docdate)-datepart(dw,sop1.docdate)) as '3'
      , max(sop1.docdate+(1-(datepart(dw,sop1.docdate)))) as '4'
      , sum(sop2.quantity) as 'Sales'
      , 0 as 'Lost Sales'      
      , 0 as 'Promotional Sales'      
      , '' as 'Customer ID'      
      , '' as 'Event Downturn'      
      , '' as 'DependentDemand'      
      , ' ' as 'LostProduction'
from #tmpBR tmp
full outer join sop10200_sop30300_union sop2 on sop2.itemnmbr=tmp.SKUItemNumber
join sop10100_sop30200_union sop1 on sop1.sopnumbe=sop2.sopnumbe
right outer join iv00102 iv2 on iv2.itemnmbr=tmp.SKUItemNumber
      and iv2.locncode='WAREHOUSE'
right outer join iv00101 iv1 on iv1.itemnmbr=tmp.SKUItemNumber
right outer join iv00103 iv3 on iv3.itemnmbr=tmp.SKUItemNumber
where
      iv1.itmclscd!='NON-IV'
      and iv1.itemtype !='2'
      and sop1.docdate>'2012-04-30'
      and sop2.soptype='2'
group by tmp.SKUItemNumber
      , iv2.primvndr
      , (case when datepart(dw,sop1.docdate)=1 then sop1.docdate
            else sop1.docdate+(1-(datepart(dw,sop1.docdate)))
            end)
order by tmp.SKUItemNumber
Avatar of jdr0606

ASKER

Unfortunately I'm still not see the number of rows I was expecting.

If the #tmpBR table has 37,400 rows and I run the script for 6 months and group by weeks I would expect to see 37,400 X 26 rows as a result set.

Could I be grouping wrong?

Thanks
ASKER CERTIFIED SOLUTION
Avatar of deighton
deighton
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account