Query Results Not Always Showing

[dwg23]

Hello,
I have written the below query and am stumped as to why some part numbers return a result and others don't.
all have the same information in the tables the only difference is in the item numbers.

item number 02fa10a gives this result.

Item Location Bin QtyOnHand Qty In Fab
02FA10A EUG STOCK 0 110
02FA10A PDX STOCK 0 110
02FA10A SEA F13-C-09 10 110
02FA10A SEA STOCK 0 110

Item number VP70007750M Gives this result.

Item Location Bin QtyOnHand Qty In Fab



Can anyone shed some light as to why this would happen?
Again both have the same information in the tables.

Select
tblimItemLocBin.Item,
tblimItemLocBin.Location,
tblimItemLocBin.Bin,
tblimItemLocBin.QtyOnHand,
Sum(tblwhWorkOrderItem.QuantityInFabrication) As 'Qty In Fab'
From
tblimItemLocBin
Inner Join tblwhWorkOrderItem On tblimItemLocBin.Item =
tblwhWorkOrderItem.Item
Inner Join tblimItem On tblimItem.Item = tblimItemLocBin.Item
Where
tblimItem.Item = 'VP70007750M'
Group By
tblimItemLocBin.Item, tblimItemLocBin.Location, tblimItemLocBin.Bin,
tblimItemLocBin.QtyOnHand

Thanks,
DWG23

 

[gmmastros]

Can you tell me how many rows you get for each of these?

Select * From tblimItemLocBin Where Item = 'VP70007750M'
Select * From tblwhWorkOrderItem Where Item = 'VP70007750M'

If I was a betting man, I would say that 1 (or both) returns 0 records. When you write an inner join query, you only get results if there is at least one match in each table that you are inner joining to.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

If I am not mistaken, the following query should give you identical results as your query "when it works". The difference here is that you should still get a row when there are 0 Qty In Fab.

Select  tblimItemLocBin.Item,
        tblimItemLocBin.Location,
        tblimItemLocBin.Bin,
        tblimItemLocBin.QtyOnHand,
        Coalesce(Counts.[Qty In Fab], 0) As [Qty In Fab]
From	tblimItemLocBin
        Left Join (
          Select   tblwhWorkOrderItem.Item,
                   Sum(tblwhWorkOrderItem.QuantityInFabrication) As [Qty In Fab]
          From	 tblwhWorkOrderItem
          Group By tblwhWorkOrderItem.Item
          ) As Counts
		  On tblimItemLocBin.Item = Counts.Item
Where	tblimItemLocBin.Item = 'VP70007750M'

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dwg23]

George,
Great! it works as it should. I should have remembered that and inner join is not always the way to go.

Thanks again.
DWG23