Sql Query Error which should return only Suppliers that are once in a other table

Question

I have an error if I try to execute it:

create View vwEinzellieferant
as
Select P.SupplierID, S.CompanyName, S.ContactName, S.Address + ' ' + S.City + ' ' + S.Region + ' ' + S.PostalCode + ' ' + S.Country, S.Phone
from Suppliers S inner join Products P on S.SupplierID = P.SupplierID
group by P.SupplierID, S.CompanyName, S.ContactName, S.Address, S.City, S.Region, S.PostalCode, S.Country, S.Phone
having (Count(S.SupplierID in (Select SupplierID from Products))) > 2;

In having there is a Problem, but I do not know what. For explanation: The Query should create a view but only with the Suppliers who are once in the Products list. Can somebody help me?

Answer 1

Seems you need to rearrange the HAVING Clause in order to return the related COUNT value equals to 1 such as

SELECT P.SupplierID,
       S.CompanyName,
       S.ContactName,
       S.Address + ' ' + S.City + ' ' + S.Region + ' ' + S.PostalCode + ' ' +
       S.Country,
       S.Phone
  FROM Suppliers S
  JOIN Products P
    ON S.SupplierID = P.SupplierID
 GROUP BY P.SupplierID,
          S.CompanyName,
          S.ContactName,
          S.Address,
          S.City,
          S.Region,
          S.PostalCode,
          S.Country,
          S.Phone
 HAVING ( SELECT COUNT(*) FROM Products WHERE SupplierID = S.ID ) = 1