Finding records with equal values in different fields?

Question

I have a people table, two of this table fields are id and country.

I want to find all records that have the same id and country, because id should be unique per country.

What would be the right way do do that?

for example, sample output should be:

+--------------+---------+
| id           | country |
+--------------+---------+
| 123          | AT      |
| 123          | AT      |
| 456          | GB      |
| 456          | GB      |
| 456          | GB      |
| 789          | AU      |
| 789          | AU      |

Please add sample data (tables) and desired output

DDS
– DDS

2019-01-23 15:03:48 +00:00
Commented Jan 23, 2019 at 15:03 — DDS
– DDS, Commented Jan 23, 2019 at 15:03
@DDS done, added example output

JohnBigs
– JohnBigs

2019-01-23 15:15:35 +00:00
Commented Jan 23, 2019 at 15:15 — JohnBigs
– JohnBigs, Commented Jan 23, 2019 at 15:15

Michał Turczyn · Accepted Answer · 2019-01-23 16:18:37Z

1

Simple GROUP BY with HAVING clause will suffice:

SELECT id, country
FROM people
WHERE id <> "" AND id IS NOT NULL
GROUP BY id, country
HAVING COUNT(*) > 1

edited Jan 23, 2019 at 16:18

answered Jan 23, 2019 at 14:52

Michał Turczyn

41.2k18 gold badges58 silver badges87 bronze badges

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

8 Comments

JohnBigs Over a year ago

this solution gives me also single fields..please see the example output that I added to the question

Michał Turczyn Over a year ago

It will give you single fields, but those fields are repeated. In your example my query would return 123 and AT, 456 and GB, 789 and AU.

JohnBigs Over a year ago

got you...you right, it would help if it was possible to exclude id != "" and id is not null, i only interested when id actually have a number

JohnBigs Over a year ago

I get Empty set . and tried elect * from suppliers where id = "123" and country = "FR"; and found results of 2 records, so the query should find those :/ @Michał Turczyn

Michał Turczyn Over a year ago

@JohnBigs Then replace people table with suppliers

|

thchp · Accepted Answer · 2019-01-23 14:50:17Z

0

    SELECT p1.* FROM people p1
    INNER JOIN people p2
    ON p1.id = p2.id AND p1.country = p2.country
    ORDERY BY p1.id

answered Jan 23, 2019 at 14:50

thchp

2,4741 gold badge22 silver badges36 bronze badges

Comments

DDS · Accepted Answer · 2019-01-23 15:17:21Z

0

this may work

 with cte as (SELECT id -- extracts ID for countryes present more than 1 time
FROM people
GROUP BY id, country
HAVING COUNT(*) > 1)

select id, country -- adds repeated values
from people p join cte on p.id=cte.id

answered Jan 23, 2019 at 15:17

DDS

2,48019 silver badges44 bronze badges

Collectives™ on Stack Overflow

Finding records with equal values in different fields?

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related