selecting duplicate IDs in mysql

Question

I have a very simple table that has just part_id and part_type:

CREATE TABLE `temp` (
`part_id` INT NOT NULL ,
`part_type` CHAR( 5 ) NOT NULL

This has a long list of part id's and types. However, some parts have more than one type. How would I get the IDs of just the parts that have more than one type? I was thinking something like this would work:

SELECT * FROM temp WHERE part_in IN (SELECT count(part_id) as duplicates FROM temp WHERE 1 GROUP BY part_id) AND duplicates > 1

however, this is clearly just psuedocode and fails. Thanks for your help.

Answer 1

select part_id from temp
group by part_id
having count(*) > 1

This will give you the part_id's with more than one row. You can then wrap that like your example query to get all of the part_id/part_type data for all parts with more than one row.

select * from temp
where part_id in (select part_id from temp
                  group by part_id
                  having count(*) > 1)
order by part_id