0

I am trying to write a query that will return rows that have two matching fields.

|-------------|-------------|----------|
|      id     |   field 1   |  field 2 |
|-------------|-------------|----------|
|      1.     |      a      |     z    | #
|-------------|-------------|----------|
|      2.     |      b      |     x    |
|-------------|-------------|----------|
|      3.     |      c      |     y    | ##
|-------------|-------------|----------|
|      4.     |      a      |     z    | #
|-------------|-------------|----------|
|      5.     |      b      |     z    |
|-------------|-------------|----------|
|      6.     |      c      |     y    | ##
|-------------|-------------|----------|

The rows marked above would be returned from such a query. They are essentially duplicates. I tried inner joining the table on itself where field 1 = field 1 and field 2 = field 2, to no avail. Can this be done without writing a PHP script with multiple queries?

Improve this question
3
  • Does this help? stackoverflow.com/questions/8149210/… Commented Nov 29, 2018 at 22:37
  • 1
    @Mark, yes that perfectly answers my question! Thank you! Commented Nov 29, 2018 at 22:41
  • If you have MySQL V8.0 or later you can use count(*) over(partition by ...) shown here Commented Nov 29, 2018 at 23:10

2 Answers 2

Reset to default
0 
SELECT t.*
FROM mytable t
INNER JOIN (
   SELECT field_1, field_2
   FROM mytable
   GROUP BY field_1, field_2
   HAVING COUNT(id)>1
) f
ON t.field_1 = f.field_1
   AND t.field_2 = f.field_2
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Sorry, I misunderstood. You are looking for something like this:

SELECT GROUP_CONCAT(t1.id), t1.f1, t1.f2 
FROM t t1 
CROSS JOIN t t2 
WHERE t1.f1=t2.f1 AND t2.f2=t1.f2 AND t1.id != t2.id
GROUP BY t1.f1, t1.f2
Improve this answer

3 Comments

This does not account for field 1. They could have B in field 1 and Y in field 2.
I went ahead an updated it. I assume you want the feidl1 and feild2 as the result, not the ids?
added group by now in case you wanted ids

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.