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
– hatchet - done with SOverflow, Commented Feb 26, 2013 at 23:52
It should return all three. Basically I am looking to return "groups" of rows that each have matching columns. — user912447
– user912447, Commented Feb 27, 2013 at 13:07

Taryn · Accepted Answer · 2013-02-26 23:44:49Z

26

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, 2013 at 23:44

answered Feb 26, 2013 at 23:32

Taryn

249k57 gold badges375 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andrew Savinykh Over a year ago

Depending on the requirements I'd suggest substituting having count(id) = 2 with having count(id) > 1 Something along these lines: sqlfiddle.com/#!3/48af7/6

hatchet - done with SOverflow · Accepted Answer · 2013-02-26 23:35:14Z

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, 2013 at 23:35

hatchet - done with SOverflow

16.3k5 gold badges49 silver badges58 bronze badges

2 Comments

user912447 Over a year ago

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.

sequence Over a year ago

This answer is shorter and much more understandable to me than the accepted answer.

dan radu · Accepted Answer · 2013-02-26 23:49:04Z

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, 2013 at 23:49

answered Feb 26, 2013 at 23:29

dan radu

2,7824 gold badges20 silver badges23 bronze badges

2 Comments

hatchet - done with SOverflow Over a year ago

you need something to avoid duplicates. As written every row will always match itself.

dan radu Over a year ago

Oops, you're right, didn't think through. Updated my answer and upvoted yours.

Collectives™ on Stack Overflow

Select Rows with matching columns from SQL Server

3 Answers 3

1 Comment

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related