CODEWITHSUNDEEP
sql
user3456053
user3456053

Reputation: 3

SQL Query; How to get only records with two matching fields

I hope I can explain this properly, it is a little confusing. I need to query for records that have the same "origin" and the same "destination". My rows will each have an origin and a destination. I need to see all those in which both match. So for instance, if there is a row with Seattle (origin) and Portland (destination) I need to see all other records with Seattle as the origin and Portland as the destination. Additionally, I need to see all records with this type of a match. So if there are records with the same origin and the same destination (not just seattle and Portland), they would also be displayed. Make sense? can you help?

Upvotes: 0

Views: 85

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

I wonder if you just want to sort the data:

select t.*
from table t
order by by origin, destination;

This will put rows with common values next to each other.

Upvotes: 0

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

If I understand well, you wanna find duplicates on some fields.

You can do

select * 
from YourTable t
join (select origin, destination
      from YourTable
      group by origin, destination
      having count(*) > 1) m
on t.origin = m.origin and t.destination = m.destination

If you don't need all the fields, you could do

select origin, destination, count(*)
from YourTable
group by origin, destination
having count(*) > 1

Upvotes: 1

Related Questions