Doubt select with Where in SQL Server!

Question

Hello! I have table relations up. I need to develop a return select: Cars, from the same manufacturer and model, which are put up for sale by more than one dealer. I arrived at the following code:

select Automoveis.Modelo
from Automoveis
join Garagens as Garagens on Automoveis.codigo = Garagens.cgcRevenda
join Revendedoras as Revendedoras on Revendedoras.cgc = Garagens.codAuto where

That code there, it returns all the cars. Now the doubt: At Where, I have no idea what to use to filter the cars that have been put up for sale by more than one dealer. Would anyone have any idea how to set up that final part? Grateful for the attention!

Answer 1

A possible solution:

SELECT A.fabricante, A.modelo, count(distinct R.cgc) as NRevendedoras
FROM Negocios AS N
JOIN Revendedoras AS R on N.cgcRevenda = R.cgc
JOIN Automoveis AS A on N.codAuto = A.codigo
GROUP BY A.fabricante, A.modelo
HAVING count(distinct R.cgc) > 1

Where Nrendedores indicates the number of dealers (distinct) that sells the same car (manufacturer and model).