Finding a keyword in duplicate values in MYSQL

Question

I am trying to find the keyword "img" in the duplicate values in my SQL database using the answer in this post: Finding duplicate values in a SQL table. I want to select those duplicate values in my database that have a "img" keyword in either of 'question', 'option' or 'solution' column.

My SQL statement looks like this:

SELECT
    `subject`, `topic`, `sub_topic`, `difficulty`,`question_number`,`question_version`, COUNT(*)
FROM
    `qz_question`
GROUP BY
    `topic`, `sub_topic`, `difficulty`,`question_number`,`question_version`
WHERE 
    `option` LIKE  '%img%' OR `question` LIKE  '%img%' OR `solution` LIKE  '%img%'

This returns me this error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE `option` LIKE '%img%' OR `question` LIKE '%img%' OR `solution` ' at line 7

How do I correct this error? Need some guidance on this.

this produces a long list of data then when I find just duplicate values without any where clauses. this produces all the rows that have "img" in them. — lakshmen, Jun 17 '15 at 13:04

score 1 · Accepted Answer · answered Jun 17 '15 at 13:04

your sql has some syntax error. I modified that error execute and see

SELECT
        `subject`, `topic`, `sub_topic`, `difficulty`,`question_number`,`question_version`, COUNT(*)
    FROM
        `qz_question`
    WHERE 
        `option` LIKE  '%img%' OR `question` LIKE  '%img%' OR `solution` LIKE  '%img%'
    GROUP BY
        `topic`, `sub_topic`, `difficulty`,`question_number`,`question_version`

These bottom SQL shows only duplicate records.

SELECT `subject`, `topic`, `sub_topic`, `difficulty`,`question_number`,`question_version`, COUNT(*) as c
    FROM
        `qz_question`
    WHERE 
        `option` LIKE  '%img%' OR `question` LIKE  '%img%' OR `solution` LIKE  '%img%'
    GROUP BY
        `topic`, `sub_topic`, `difficulty`,`question_number`,`question_version`
        HAVING c > 1;

Thank you.

meaning after filter the record and then group by the records. its actual SQL syntax. — Venkatesh Panabaka, Jun 17 '15 at 13:30

Finding a keyword in duplicate values in MYSQL

1 Answers1