Please help with query - left join same table twice different conditions and count

Worked on this yesterday afternoon and all morning today; guess it's time to get help :confused:
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!

If I write it like this, it works. I get 3 released, 1 remaining. The problem with this rewrite is I have a long, complicated stored procedure that derives additional information based on that table I am joining to twice. It's fine if I have to rewrite the sproc but the road I am going down now is the same road as yesterday afternoon and it seemed like a knee-jerk reaction then. But this morning after more analysis, I'm thinking maybe the rewrite is necessary. I would just like to know for sure that I cannot get the results I want if I keep going with my original design. Thoughts from you experts, please?

Code:

---

SELECT   
        ma.Control AS MasterControl,
        (SELECT COUNT(SerialNumber) FROM xtblMROrderSerialNumbers WHERE MROrderMasterControl = ma.Control AND ReleasedMethod IS NOT NULL) ,
        (SELECT COUNT(SerialNumber) FROM xtblMROrderSerialNumbers WHERE MROrderMasterControl = ma.Control AND ReleasedMethod IS NULL)
FROM xtblMROrder AS ma
WHERE (ma.relJobControl = 131297 and bSerial = 1)

---

Spitballing, but is it safe to assume that UnreleasedItemCount always equals TotalItemCount - ReleasedItemCount? If so, that might be a simpler approach.

Thanks, as I said in post #2 I came up with a different way to write the query. Mostly I'm curious what is wrong with the query in post #1.

Quote:

---

Originally Posted by MMock

Thanks, as I said in post #2 I came up with a different way to write the query. Mostly I'm curious what is wrong with the query in post #1.

---

Mistake: You join first, and count then
Solution: Count first then JOIN

Untested

Code:

---

WITH
  RLS AS (SELECT MROrderMasterControl, COUNT(MROrderMasterControl) AS CountReleased FROM xtblMROrderSerialNumbers WHERE ReleaseMethod IS NOT NULL GROUP BY MROrderMasterControl),
  NRLS AS (SELECT MROrderMasterControl, COUNT(MROrderMasterControl) AS CountNotReleased FROM  xtblMROrderSerialNumbers WHERE ReleaseMethod IS NULL GROUP BY MROrderMasterContro)

SELECT Ma.Control, RLS.CountReleased, NRLS.CountNotReleased
FROM xtlbMROrder AS Ma
LEFT JOIN RLS ON Ma.Control=RLS.MROrderMasterControl
LEFT JOIN NRLS ON Ma.Control=NRLS.MROrderMasterControl

---

More detailed answer:
Bottom Line: In your first try you basically get a Cross Join between the 3 Rows from the First LEFT JOIN with the 1 row from the second LEFT JOIN.

There might be a workaround to "use" your first approach.

I'm currently fighting with the Forum to insert SQL-Statements here....*GROWL*

Didn't we had this problem some months ago?

Code:

---

SELECT Ma.Control,
SUM(C ASE WHEN RLS.ReleaseMethod IS NULL THEN 0 ELSE 1 END) AS SumRelease,
SUM(C ASE WHEN RLS.ReleaseMethod IS NOT NULL THEN 0 ELSE 1 END) AS SumReleaseNo 
FROM xtlbMROrder AS Ma
LEFT JOIN xtblMROrderSerialNumbers AS RLS ON Ma.Control=RLS.MROrderMasterControl
GROUP BY Ma.Control

---

How it works compared to your try:
The LEFT JOIN returns 4 Rows (!!), replaces depending on ReleaseMethod the NULL/NOT-NULL-Value with 0 or 1 respectively, THEN i build the SUM (Which in the end is the same as a count), and then Group by

EDIT: *GROWL*
It's the Word "C ASE"
We had that same thing a few months ago

Thanks for putting some time into this! I will take a look at it at least by the end of the week.

OK, have to amend my answer above: It's not a Cross Join alone, but there is a second factor.

In your first try, leave out the "Count" and "Group by", then you will see it

This is SQL for the Bogus-Data i had

Code:

---

WITH
    MA AS (SELECT 36862 AS Control) --simulating MasterTable
   
SELECT MA.Control, RL.Control AS RLControl, RL.ReleaseMethod AS RLMethod, NR.Control AS NRControl, NR.ReleaseMethod  AS NRMethod
FROM MA
LEFT JOIN tbl_mmock AS RL ON MA.Control=RL.Control AND RL.ReleaseMethod IS NOT NULL
LEFT JOIN tbl_mmock AS NR ON MA.Control=NR.Control AND NR.ReleaseMethod IS NULL

---

Returns

Control	RLControl	RLMethod	NRControl	NRMethod
36862	36862	G	36862
36862	36862	G	36862
36862	36862	G	36862

You are COUNT-ing RLControl and NRControl, which you get 3 for both criteria
Remember: In your first Try, the COUNT (and its Group By) goes on the complete Result you get from the Joins, so the result is actually "correct": two times "3"
It's just not what you expected.

Bottom line: Either Count first, join second (Your approach with the 2 CTE's),
or my second approach with SUM instead of count.
Note: No filtering whatsoever on ReleaseMethod in WHERE or ON-Clause here. The "Transformation" happens on the fly for each row
Depending on the amount of data, i wouldn't be surprised in differences in performance
Remember to put an Index on the ForeignKey for the JOIN to run better