How to Compare Rows in SQL?

How to compare multiple rows of same column in sql?

• Table is Product and it has (maker, model, type) column. It is assumed that model numbers in the Product table are unique for all the makers and product types. Makers column has values A,B,C,D and Type column has values PC,Laptop, Printer. Example maker model type A 1232 PC A 1233 PC A 1276 Printer A 1298 Laptop A 1401 Printer A 1408 Printer A 1752 Laptop B 1121 PC B 1750 Laptop C 1321 Laptop D 1288 Printer D 1433 Printer E 1260 PC E 1434 Printer E 2112 PC E 2113 PC I need to find out makers who produce only the models of the same type, and the number of those models exceeds 1. Result should be maker type D Printer I've just started learning sql and I got stuck on this query so please help, thanks :)

• Answer:

  I can't write the SQL for you at the moment (too tired and lazy), but the way to get the answer is to create two sub-queries, one asking how many models each maker makes (i.e. counts rows by maker), the second asking how many types each maker makes (i.e. counts rows by maker and model). Once you've got those sub-queries you can join the rows that have the same maker and counts, and display the maker and model. So D makes two models and there are two D Printers, so that will be the only row that prints. Another approach would be something like SELECT * FROM Table AS X WHERE NOT EXISTS (SELECT * FROM Table AS Y WHERE Y.Maker = X.Maker AND Y.Type != X.Type) .... (For which there will be an equivalent JOIN query)