Re: Duplicate check
This is kind of wrong in the sense that of course you can't have
duplicate primary keys. I was tired when I answered this last night.
So you put all of the parts of the key fields and the non-key field
togethers that make up the duplicate case. In your case it looks like
key parts 1-4 and field 6. You then compare the last part of your key
to make sure it is different so you know you are actually looking at 2
different records.
select
a.*,
b.*,
from
table1 a,
table2 b
where
a.k1 = a.k1 and
a.k1 = b.k1 and
a.k1 = b.k1 and
a.k4 = b.k4 and
a.f6 = b.f6 and
a.k5 < b.k6 -- It can be greater than if you want it just changes the
meanings of the two tables. If you use <> you will get the same pair of
rows twice in your output.
;
bozon wrote:
> You can also do a self join set the primary keys equal and the non key
> fields not equal call these non key fields f9, f11, f12
>
> select
> a.*,
> b.*
> from
> table1 a,
> table2 b
> where
> a.k1 = b.k1 and
> a.k2 = b.k2 and
> a.k3 = b.k3 and
> (a.f9 <> b.f9 or a.f11 <> b.f11 or a.f12 <> b.f12)
>
> I don't think this is any faster.
>
> You can use PDQPRIORITY to make yours faster.
>
> Oh, and never select anything in your exists
>
> exists(select * from table1 where something = somethingelse)
>
> Informix probably knows to ignore the fields you are selecting, but I
> think other database used to key off of the * as a special case with
> exists. It let the optimizer choose any path.
>
>
> Kennedy, Randy wrote:
> > IDS: 9.4FC3
> > O/S: HPUX 11.11
> >
> > I am trying to determine the best query to use for a view showing only
> > duplicate records.
> >
> > fields in view char(1),smallint,char(2),int, date (which together are
> > the pk of table) I'll call these fields 1-5
> >
> > Duplicate checking fields are 1st 4 from pk (which identify a particular
> > case) and another int (field 6) that holds a action code.
> >
> > I want to check a single case for a duplicate of the action code and
> > return the case and dates of occurrences.
> >
> > Query I have now is:
> >
> > select 1,2,3,4,5
> > from table1 x0
> > where EXISTS (select 1,2,3,4,6
> > from table1 x1 where x1.1= x0.1AND x1.2= x0.2AND x1.3= x0.3
> > AND x1.4= x0.4AND x1.6= x0.6
> > group by 1,2,3,4,6
> > having count(*) > 1 AND x0.6= value I am dupe checking for);> >
> > Thanks,
> > Randy
bozon wrote > a.k5 < b.k6 -- It can be greater than if you want it just changes the This is of course a.k5 < b.k5. I hate typos but I make a lot of them.