Worked on this yesterday afternoon and all morning today; guess it's time to get help
I have a table which contains high level information on an order placed by a customer - xtblMROrder
I have another table xtblMROrderSerialNumbers which contains 1 or more rows that fulfill the order.
When we receive from our vendor the items, we create rows in xtblMROrderSerialNumbers. Also in this table is a column ReleasedMethod when we send the items out to the customer. It is null if we haven't released that item yet.
So here's the state of my data.
1) I have a row in xtlbMROrder with key Control = 36862 and QuantityOrdered = 4.
2) I have 4 rows in xtblMROrderSerialNumbers that are MROrderMasterControl = 36862. Three items have been released to the customer, one has not. Picture below. (I x'd out Serial Number but they are unique).
I want to get a count of released items (3) and remaining items (1). My query is giving me a value of 3 for both counts. I cannot figure out what is wrong. I do know that if I comment out one of the COUNT's and its associated table, I get the correct value in the other count. So it has to have something to do with my join to the same table twice but I don't see it.
Thank you!
Reply With Quote