Ann W. Harrison wrote:

> I still think my query returns all the pairs - and some unnecessary rows
> when the same subject appears in more than two dbnotes records. Perhaps
> it's the combination of folder and noteid that is the unique identifier.
> If that's the case then this query will work better
>
> select a.noteid as noteid1, a.folderid as folderid1,
> b.noteid as noteid2, b.folderid folderid2, a.subject
> from dbnotes a
> join dbnotes b on (a.subject = b.subject)
> where a.noteid < b.noteid or a.folderid < b.folderid
>
> The data could look like this:
>
> noteid folder string
>
> 11 1 abc
> 11 2 abc
> 12 1 abc
>
> The result would be:
>
> noteid1 noteid2 folderid1 folderid2 string
> ------- ------- --------- --------- ------
> 11 11 1 2 abc
> 11 12 1 1 abc

Actually, the result of the above query would be:

noteid1 folderid1 noteid2 folderid2 string
------- --------- ------- --------- ------
11 1 11 2 abc
11 1 12 1 abc
12 1 11 2 abc

Things like this quickly become messy, that's why I prefer selecting
from only one alias, a combination of NOT EXISTS or use EXISTS when
there is a possibility of triplicates or more (it could also be
considered a reason for using single field PKs). I remember having
written too long queries (and I mean too long, I think I in my first
attempt passed a 32Kb limit) with lots of LEFT JOINS with NOT EXISTS to
- amongst other things - flatten contents of tables.

Set