CODEWITHSUNDEEP
mysqlsql
nulldef
nulldef

Reputation: 1

Selection by repeated field value

There is the table PETS

id | field_a | field_b

Need to make a selection by coincidence one field, and that was more than a coincidence. for example 

id | field_a | field_b
1  | cats    | 13
2  | cats    | 15
3  | cats    | 16
4  | dogs    | 15
5  | dogs    | 16
6  | birds   | 13
7  | birds   | 19
8  | birds   | 14

As a result, have obtained

cats
dogs

This result is obtained because only these records match more than one entry in the field_b (15 and 16).

Upvotes: 0

Views: 66

Answers (1)

Tarsis
Tarsis

Reputation: 740

You could try a query like this:

SELECT p1.field_a 
FROM pets p1 
INNER JOIN pets p2 ON (p1.field_b = p2.field_b AND p1.id != p2.id) 
GROUP BY p1.field_a,p2.field_a 
HAVING count(p1.field_b) > 1

Upvotes: 1

Related Questions