SQL query related to Joins and count

Please clarify my sql query question.

I have 4 tables in my sql server example as below:

Products ---> Id, productname
Suppliers ---> Id, ProductId, suppliername
Customer --> Id, supplierId, customername, orderId
Orders --> Id, ordername

Input to my stored procedure is productId.

I want to fetch All suppliers whose productid is my sqlproc parameter (input) and Count of OrderIds with ordername = ‘orderorange’ —

Answer

SELECT ProductId, productname, suppliername,
    (SELECT COUNT(*) FROM Customer WHERE Id = 1 and Customer.supplierId = Suppliers.Id) AS CountCustomer1
    (SELECT COUNT(*) FROM Orders WHERE ordername = 'orderorange') AS CountOrderOrange
FROM Suppliers
WHERE ProductId = ?