Search code examples
mysqlsqlselectwhere-in

Comapare values in same column same table

My table is

Product(maker, model, type)

I want to select type and maker of only the same type and also having count of models greater than one.

Sample data:

maker             model         type
-------------------------------------
 A                 123        computer
 B                 234         laptop
 B                 345         laptop
 C                 456         printer
 C                 543           PC

The answer for the above sample data is B and laptop because maker B manufactures only laptops and also more than one model.It is not maker A because even though he manufactures product of same type he has only one model.

Solution

  • That's easy. You are looking for makers with a record count > 1 and a count of distinct types = 1:

    select maker
from product
group by maker
having count(distinct type) = 1 and count(*) > 1;