Not sure if the title is descriptive enough (sorry about the bad english), but my problem is that I need to find the rows that have the same value in all rows.
Possibly easier to understand what I actually mean with an example: I have an table that's called Catalogue with the attributes motortype, motorpart and partnumber where motortype and motorpart are VARCHAR and partnumber are int.
Some partnumbers are used in all the motortypes, and it's those partnumbers that I am interested in finding.
I'm having a hard time seeing how to solve this, so any help would be greatly appreciated!
EDIT: Sorry if the question was lackful or bad. I have updated with the table schema, some sample data and my desired results under:
CREATE TABLE Catalogue (
motortype VARCHAR,
motorpart VARCHAR,
partnumber int,
PRIMARY KEY (partnumber)
);
Sample data:
INSERT INTO Catalogue VALUES ('M1', 'Brakes', 1);
INSERT INTO Catalogue VALUES ('M2', 'Brakes', 1);
INSERT INTO Catalogue VALUES ('M3', 'Brakes', 1);
INSERT INTO Catalogue VALUES ('M1', 'Gasket', 2);
INSERT INTO Catalogue VALUES ('M2', 'Gasket', 2);
INSERT INTO Catalogue VALUES ('M3', 'Pedal', 3);
Desired result:
| motorpart | partnumber |
|-----------|------------|
| Brakes | 1 |
Hope this was better formulated!
If there are 3 different types you can use next query:
select motorpart
from Catalogue
group by motorpart
having count(distinct motortype) >= 3;
If you don't know the number:
select motorpart
from Catalogue
group by motorpart
having count(distinct motortype) = (select distinct motortype
from Catalogue);
Rextester here