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?
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
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
SELECT
pid
FROM tablename
where fid IN (19,30)
group by
pid
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
