Getting more details out of a SQL subquery

Question

I have a SQL Query giving me a list of double records in my database.

select periodid, itemid from periodscore  
group by periodid, itemid
having count(*) > 1

This works as expected, but now I would like to retrieve additional fields of these records (such as date last updated etc). So I tried:

select * from periodscore where periodscoreid in
(select periodscoreid from periodscore  
group by periodid, itemid
having count(*) > 1)

Of course this doesn't work and gives me the error:

Column 'periodscore.PeriodScoreID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

How can I retrieve the extra fields in this query?

Answer 1

select ps.* 
from periodscore ps
inner join (
    select periodid, itemid 
    from periodscore   
    group by periodid, itemid 
    having count(*) > 1
) psm on ps.periodid = psm.periodid and ps.itemid = psm.itemid

Answer 2

  select p1.* from periodscore p1 JOIN 
  (select periodid, itemid from periodscore  
   group by periodid, itemid
   having count(*) > 1) p2
   ON (p1.periodId = p2.periodId
       AND p1.itemid = p2.itemid)

if periodid or item have null values then

  select p1.* from periodscore p1 JOIN 
  (select periodid, itemid from periodscore  
   group by periodid, itemid
   having count(*) > 1) p2
   ON (IFNULL(p1.periodId,0) = IFNULL(p2.periodId,0))
       AND IFNULL(p1.itemid,0) = IFNULL(p2.itemid,0))