Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

finding duplicate - so close

Status
Not open for further replies.

jez

Programmer
Apr 24, 2001
370
VN
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
 
yep... knew it ... it was a right outer join, not left.

so fully functional version is.

Code:
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;
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top