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

What should happen if three rows all have the same Part_Type and Station_Type? — hatchet - done with SOverflow, Feb 26 '13 at 23:52
It should return all three. Basically I am looking to return "groups" of rows that each have matching columns. — user912447, Feb 27 '13 at 13:07

Taryn · Accepted Answer · 2013-02-26T23:44:49.607

25

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

edited Feb 26 '13 at 23:44

answered Feb 26 '13 at 23:32

Taryn

242,637
56
362
405

2

Depending on the requirements I'd suggest substituting `having count(id) = 2` with `having count(id) > 1` Something along these lines: http://www.sqlfiddle.com/#!3/48af7/6 – Andrew Savinykh Feb 26 '13 at 23:42

score 4 · Answer 2 · answered Feb 26 '13 at 23:35

4

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)

answered Feb 26 '13 at 23:35

hatchet - done with SOverflow

15,919
5
47
57

Both yours an bluefeet's answer works and the only reason I am accepting his is for the "group by" he put in. It makes the information a little easier to read. – user912447 Feb 27 '13 at 13:12
This answer is shorter and much more understandable to me than the accepted answer. – sequence Jan 15 '22 at 18:25

dan radu · Answer 3 · 2013-02-26T23:49:04.970

1

I think a self-join will work for you:

SELECT * FROM table t1 
INNER JOIN table t2 ON t1.Part_Type = t2.Part_Type 
  AND t1.Station_Type = t2.Station_Type
  AND t1.Id <> t2.Id

edited Feb 26 '13 at 23:49

answered Feb 26 '13 at 23:29

dan radu

2,772
4
18
23

2

you need something to avoid duplicates. As written every row will always match itself. – hatchet - done with SOverflow Feb 26 '13 at 23:32
Oops, you're right, didn't think through. Updated my answer and upvoted yours. – dan radu Feb 26 '13 at 23:47

Select Rows with matching columns from SQL Server

3 Answers3

Linked