finding duplicate - so close

[jez]

select o.operanum from opera_num o
left outer join (
  select p.operanum, count(p.operanum) as c
  from opera_num p
  where to_days(expiry)>to_days(now())
  GROUP by p.operanum
  having count(p.operanum)>1
)as secondUse
on o.operanum = secondUse.operanum
where to_days(o.expiry)>to_days(now());

Hi everyone,
I think i am close to getting this right, but i cant see where its going wrong.

Firstly the inner query (inside the join) returns all the values in the field operanum that have been seen before.
What i want to do with the outer query is find all the other places it has been seen.
So i am trying to find all instances of values in that field that are not unique.

Can anyone show me how this is going wrong as i am getting back every row in the table from this query.

Also it is version 4.1

Thanks in advance

Jez

 

[jez]

yep... knew it ... it was a right outer join, not left.

so fully functional version is.

select o.operanum, tel1num from opera_num o
right outer join (
  select p.operanum, count(p.operanum) as c
  from opera_num p
  where to_days(expiry)>to_days(now())
  GROUP by p.operanum
  having count(p.operanum)>1
)as secondUse
on o.operanum = secondUse.operanum
where to_days(o.expiry)>to_days(now())
order by o.operanum;