Select Rows with matching columns from SQL Server

Question

I am fairly certain that this is something simple, but every example I have tried is failing. I want to query a table like this

ID   Part_Type   Station_Type
---  ---------   ------------
1    5           234
2    5           846
3    5           234
4    6           585
5    6           585
6    7           465

and return the rows 1 and 3, as well as 4 and 5. That is, I want to return rows where two of their columns match. It is similar to this question: SO Question but needs to be done on one table only. That query will find a match for every row, but I only want rows that have matching values in two columns. How do I go about find that?

Thank you

Answer 1

You can use the following:

select t1.id, t1.part_type, t1.station_type
from yourtable t1
where exists (select part_type, station_type
              from yourtable t2
              where t1.part_type = t2.part_type
                and t1.station_type = t2.station_type
              group by part_type, station_type
              having count(id) > 1)

See SQL Fiddle with Demo

Answer 2

select id, part_type, station_type 
from myTable t1
where exists (select 1 from myTable t2
              where t1.part_type = t2.part_type
                  and t1.station_type = t2.station_type
                  and t1.id <> t2.id)