CODEWITHSUNDEEP
phpmysqlsql
SAGAR.GUDETI
SAGAR.GUDETI

Reputation: 41

mysql get duplicate rows using where

pid fid
6   19
7   19
8   19
9   19
7   30
6   30

I have a table like this. I want to select duplicate rows, when I'm sending 19, 30 ids Using an IN clause like:

Select pid from tablename where fid IN (19,30);

I want these results 

pid
7
6

Is there any mysql statement to get these results?

Upvotes: 0

Views: 121

Answers (4)

ahmetunal
ahmetunal

Reputation: 3950

First, you should group pid's and count them, and then take the ones with more than one occurence

SELECT pid, count(*) AS cnt
FROM tablename
WHERE fid IN (19,30)
GROUP BY pid
HAVING cnt > 1

Upvotes: 0

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

SELECT 
    pid 
FROM tablename 
where fid IN (19,30)
group by 
pid

Upvotes: 0

John Woo
John Woo

Reputation: 263693

SELECT pid
FROM tableName
WHERE fid IN (19,30)
GROUP BY pid
HAVING COUNT(*) = 2

if unique constraint was not defined on fid for each pid, then you need to have DISTINCT inside COUNT

SELECT pid
FROM tableName
WHERE fid IN (19,30)
GROUP BY pid
HAVING COUNT(DISTINCT fid) = 2

Upvotes: 3

podiluska
podiluska

Reputation: 51494

Use distinct

Select distinct pid from tablename where fid IN (19,30);

or to find the duplicates

select pid from tablename group by fid having count(*)>1

Upvotes: 0

Related Questions