Okay, next try.

If you want the count of visits of your customers within the special_orders use a aggregated sub-query. (In your sample data every customer/location has exactly two entries within the orders).

SELECT so.CUSTOMER_ID, so.LOCATION_ID, o.visit_count

FROM #SPECIAL_ORDERS so

JOIN (SELECT CUSTOMER_ID, LOCATION_ID, COUNT(*) visit_count

FROM #ORDERS

GROUP BY CUSTOMER_ID, LOCATION_ID) o

ON so.CUSTOMER_ID = o.CUSTOMER_ID AND so.LOCATION_ID = o.LOCATION_ID

Greets

Flo