June 18, 2003 at 9:02 pm
Hi folks,
Let me know if somebody can help me out. Here is my query :-
Select distinct Case30.caseno,case30.fname,case30.lname,case30.date,file30.reportid,file30.caseid from Case30 Left outer join file30 on case30.caseno = file30.caseid
Union
Select Distinct Caseold.caseno,caseold.fname,caseold.lname,caseold.date,file30.reportid,file30.caseid from Caseold Left outer join on caseold.caseno = file30.caseid
Still i get duplicate values. I tried group by too. I hope somebody can help
June 18, 2003 at 11:21 pm
That is odd, maybe if you analize the problem like this, you'll find that your duplicates aren't really duplicates
select
CaseAll.caseno,
CaseAll.fname,
CaseAll.lname,
CaseAll.date,
file30.reportid,
file30.caseid
from
(
select
Case30.caseno,
case30.fname,
case30.lname,
case30.date
from Case30
Union
Select
Caseold.caseno,
caseold.fname,
caseold.lname,
caseold.date
from Caseold
) as CaseAll
Left outer join file30
on CaseAll.caseno = file30.caseid
Please find out if your duplicates are stil there, by using this first:
select
Case30.caseno,
case30.fname,
case30.lname,
case30.date
from Case30
Union
Select
Caseold.caseno,
caseold.fname,
caseold.lname,
caseold.date
from Caseold
Maybe some kind of summarizing is missing, like Max(casexxx.Date)
June 19, 2003 at 1:30 am
Also the select distinct is not required - union would return distinct rows by itself
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply