Code:
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