looking for duplicate values within multiple columns mysql-CodePudding

I would love to find a simple query to find duplicate values across more that one column in a table.

table example:

PK, FK, Animal1, Animal2
001, 100, Dog, Cat
002, 100, Dog, Bird
003, 100, Rat, Mouse
004, 100, Fish, Dog
005, 200, Dog, Mouse

I want to find the rows where "Dog" is duplicated in both Animal1 and Animal2. So, in this case it would return 001, 002, 004... FK 100 can only select Dog once.

CodePudding user response：

select FK, Animal, count(a.PK) count from (
    select PK, FK, Animal1 Animal from table1
    union
    select PK, FK, Animal2 from table1) a
group by FK, Animal

dbfiddle with your example table here

CodePudding user response：

I think what you need a self UNION. And, if by simple, you mean using only a single WHERE filter, then this question isn't terribly far off from: Using a single common WHERE condition for UNION in SQL

If you wanted only PK, FK, and whichever animal value that matches 'Dog' in a single row, you can use:

SELECT * 
FROM (SELECT animals_tt.PK, animals_tt.FK, animals_tt.Animal1 AS all_animals FROM animals_tt 
UNION 
SELECT animals_tt.PK, animals_tt.FK, animals_tt.Animal2 AS all_animals FROM animals_tt) AS animals
WHERE animals.all_animals = 'Dog';

sqlfiddle

That UNION clause creates a table with both animal columns in a single column called all_animals:

PK, FK, all_animals

001, 100, Dog

002, 100, Dog

003, 100, Rat

004, 100, Fish

005, 200, Dog
-------------------- 
001, 100, Cat

002, 100, Bird

003, 100, Mouse

004, 100, Dog

005, 200, Mouse

From there we use that single WHERE statement to filter to just rows with 'Dog'.

If there was a row with Dog, Dog, it would only return one with that query. If you needed to retain both animal rows on your filter you can include each Animal column without matching aliases but you'll need to filter each column using OR:

SELECT * 
FROM (SELECT * FROM animals_table 
UNION
SELECT * FROM animals_table) AS animals
WHERE animals.Animal1 = 'Dog'
OR animals.Animal2 = 'Dog';

sqlfiddle