CODEWITHSUNDEEP
sqlsql-server
Peter Kellner
Peter Kellner

Reputation: 15508

Need query to figure out when car model name shared by two makes

I've got a simple table with fields make and model.

I'm trying to figure out a query that will tell me when I have a model that is shared by more than 1 make. That is, if ford and gm both made a car model named "rocket", I want to know that.

I'm using Sql Server but I don't think that really matters.

I made a wild guess of course not right.

select make,
   model
group by make,
     model
where count(MODEL) > 2

Upvotes: 1

Views: 80

Answers (1)

SqlZim
SqlZim

Reputation: 38063

You are close, this will get you the make and model for each model that has more than one make if the table t consists of unique pairs of make and model. 

select make, model
from t
where model in (
  select model
  from t
  group by model
  having count(*) > 1
)

If make and model are not unique pairs for the table, you could use count(distinct make) instead:

select make, model
from t
where model in (
  select model
  from t
  group by model
  having count(distinct make) > 1
)

You could also use an exists() query instead of performing aggregation like so:

select make, model
from t
where exists (
  select 1
  from t as i
  where i.model=t.model
    and i.make<>t.make
)

Upvotes: 1

Related Questions