Re: eliminate duplicate in JOIN

From: Michael Austin <maustin_at_firstdbasource.com>
Date: Sun, 05 Apr 2009 12:52:45 -0500
Message-ID: <Kb6Cl.4942$Lr6.2217_at_flpi143.ffdc.sbc.com>

Carl Forsman wrote:

> On Sun, 05 Apr 2009 10:40:27 +0200, Shakespeare <whatsin_at_xs4all.nl>
> wrote:
> 
>> Carl Forsman schreef:

>>> The following query will return Triple word from a table like the
>>> following:
>>>
>>> pretty, woman, a
>>> a, pretty woman
>>> china,great, wall
>>> wall, great, china
>>> great, wall, china
>>>
>>> =============================
>>> How can I elimate rows that contain the same words?
>>> The output should be:
>>>
>>> pretty, woman, a
>>> wall, great, china
>>> -OR-
>>> a, pretty woman
>>> wall, great, china
>>> -OR-
>>> a, pretty woman
>>> great, wall, china
>>>
>>> ... etc
>>>
>>> =============================
>>> The output should not have 2 row containsexact 3 words
>>>
>>> For example, if I have 3 words ( pretty + woman + a )
>>> pretty, woman, a
>>>
>>> Then the output will not contain (a + pretty + woman)
>>> a, pretty woman
>>>
>>> =============================
>>> SELECT DISTINCT A.word As word1, B.word AS word2, C.word AS word3
>>> FROM Data AS A
>>> JOIN Data AS B
>>> ON A.document = B.document
>>> AND A.position < B.position
>>> AND ABS(A.position - B.position) < 3
>>> JOIN Data AS C
>>> ON A.document = C.document
>>> AND A.position < C.position
>>> AND B.position < C.position
>>> AND ABS(A.position - C.position) < 3
>>> ORDER BY word1;
>>>

>> By only accepting sequences that are in alphabetic order, but then you 
>> may have to circumvent duplicate words.
>>
>> Shakespeare
> thanks for the advice!
> 
> but how to do it in the above query?
> 
> or what command to use?

First do not MULTI-POST - use CROSS-POST (learn the difference)- I think you hit every db NG you could find...

As stated in another NG - fix your design so you do not have this problem by having a unique doc_id in this table so one and only one entry exist so you never have this problem. Received on Sun Apr 05 2009 - 12:52:45 CDT