Lol! it actually worked when I used IN instead of = but the data that was returned wasn't correct, the second bit works fine.
That's because the subquery in Kapil's code
and it.bitIsAsset = Case @bitAsset When 1 Then (SELECT it.intItemId FROM dtlItem it WHERE it.intItemId IS NOT NULL) Else 'All' End
isn't correlated to the outer SELECT - it can return any old rows so long as intItemId IS NOT NULL.
Note that the LEFT JOIN to dtlItem will be converted to an INNER JOIN by referencing columns in the WHERE clause (unless you are checking for a NULL).
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw
For fast, accurate and documented assistance in answering your questions, please read this article
.Understanding and using APPLY, (I)
and (II) Paul White Hidden RBAR: Triangular Joins
/ The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff ModenExploring Recursive CTEs by Example Dwain Camps