CODEWITHSUNDEEP
postgresql
Breinne
Breinne

Reputation: 23

Finding rows that have the same value with Postgres

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!

Upvotes: 1

Views: 2730

Answers (1)

McNets
McNets

Reputation: 10827

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

Upvotes: 1

Related Questions