CODEWITHSUNDEEP
mysqlsqldatabasejoin
good112233
good112233

Reputation: 138

show duplicate data mysql and where clause

i have table and records like this

my table

i want show duplicate code data with WHERE place is a ,i want foo and foo 2 to show because them duplicate but foo 2 not show

i can show all duplicate data with this query : 

SELECT * FROM myTable
         WHERE code 
              IN (SELECT * FROM (SELECT code
                 FROM myTable
                 GROUP BY code
                     HAVING COUNT(code) > 1)
                  AS a)

but when add where

SELECT * FROM myTable
         WHERE code 
              IN (SELECT * FROM (SELECT code
                 FROM myTable
                 GROUP BY code
                     HAVING COUNT(code) > 1)
                  AS a) AND place = 'a'

only show foo , i want foo 2 to appear too because both them is duplicate

--------UPDATE-----------

fixed with 

SELECT * 
FROM myTable m1
WHERE exists 
    (
        SELECT code
        FROM myTable m2
        where m1.code = m2.code
        and place = 'a'
)

but this not filter duplicate data or not, so you need to combine with WHERE IN

SELECT * FROM myTable
         WHERE code 
              IN (SELECT * FROM (SELECT code
                 FROM myTable
                 GROUP BY code
                     HAVING COUNT(code) > 1)
                  AS a)
AND exists 
    (
        SELECT code
        FROM myTable m2
        where m1.code = m2.code
        and place = 'a'
)

but still slow to execute big data, WHERE IN can change with INNER JOIN

SELECT * 
FROM myTable m1
INNER JOIN (
    SELECT code
    FROM myTable 
    GROUP BY code
    HAVING COUNT( code ) > 1
) m2 ON m1.code=m2.code WHERE exists 
    (
        SELECT code
        FROM myTable m2
        where m1.code = m2.code
        and place = 'a'
)

and to make more fast just add WHERE after INNER JOIN and not use WHERE IN or WHERE EXISTS

SELECT * 
FROM myTable m1
INNER JOIN (
SELECT code
    FROM myTable 
    GROUP BY code
    HAVING COUNT( code ) > 1
) m2 ON m1.code=m2.code 
WHERE 
    (m1.place = "a" OR m2.place = "a")

but this will trow error SELECT list is not in GROUP BY clause and contains nonaggregated column … incompatible with sql_mode=only_full_group_by and this solution will fix that

Upvotes: 1

Views: 46

Answers (2)

zealous
zealous

Reputation: 7503

Try the following with exists. here is the demo.

SELECT * 
FROM myTable m1
WHERE exists 
    (
        SELECT code
        FROM myTable m2
        where m1.code = m2.code
        and place = 'a'
)

Output:

| id   | code   | name  | place |
| ---- | ------ | ----- | ----- |
| 1001 | 110004 | foo   | a     |
| 1002 | 110004 | foo 2 | b     |

Upvotes: 2

Rajat Jaiswal
Rajat Jaiswal

Reputation: 643

The other workaround you can do with the following query

      SELECT tmp.*
      FROM mytable tmp
      INNER JOIN mytable tmp1 ON tmp1.Code = tmp.Code
                 And tmp1.id < tmp.Id
      WHERE tmp1.place = 'a'

Upvotes: 0

Related Questions