EDIT: I notice you're adding more to your post. I'll check it again - thanks!!

What is interesting is I can remove the additional NOT where clause in statement number 2, and move it into the SELECT statement as a CASE that evaluates to 1 or 0 to determine if it's that customer/number, product type combination.

I run this, and the new column works fine, and the statement runs quick.

Then I wrap it as a sub query and ask it to select * from that sub query table where the CASE statement column is 0 or 1 (either, pick one), and it also hangs indefinitely.

Such as:

SELECT *

FROM

(

SELECT CASE WHEN cus_no IN ('1','2')

AND product_type <> 'BIKE' THEN 1 ELSE 0 END AS the_column

FROM [aforementioned tables]

) subq

WHERE the_column = 1

Run just the inner subquery, it runs quick. Run the whole statement, it locks up again.