Aggregate count and sum in SQL

In SQL we can use GROUP BY to create sets of results to aggregate the values in each set, when you want to limit the Groups with an aggregate function we use HAVING to express the desired filter condition.

The first step is to construct a query that resolves the Model that was sighted at each Spot:

SELECT t3.license_plate, t1.model, t3.Spot
FROM table3 t3 
INNER JOIN table2 t2 ON t3.license_plate = t2.license_plate 
INNER JOIN table1 t1 ON t2.vin = t1.vin

It is always a good idea to inspect this raw result set first, to ensure that your joins are expressed correctly, this will result in the following:

From that result set, we expect to find that Renault Car is spotted multiple times, we can use GROUP BY and the COUNT function to calculate the number of spot values that each model was sighted at:

SELECT t1.model, COUNT(t3.Spot) as [Spots]
FROM table3 t3 
INNER JOIN table2 t2 ON t3.license_plate = t2.license_plate 
INNER JOIN table1 t1 ON t2.vin = t1.vin
GROUP BY t1.model

This returns:

Finally, we can use the HAVING clause to filter the results based on the result of the COUNT() aggregate function:
multiple can be expressed in SQL as "more than 1"

SELECT t1.model
FROM table3 t3 
INNER JOIN table2 t2 ON t3.license_plate = t2.license_plate 
INNER JOIN table1 t1 ON t2.vin = t1.vin
GROUP BY t1.model
HAVING COUNT(Spot) > 1

You can see this in action here: http://sqlfiddle.com/#!18/26ed4/2
In the fiddle the number of spots is also included in the output.

You can use aggregation -- after joining the tables together of course:

select t1.model
from table3 t3 join
     table2 t2
     on t3.license_plate = t2.license_plate join
     table1 t1
     on t2.vin = t1.vin
group by t1.model
having min(spot) <> max(spot);