Hi,

this statement:

<sql>
 select MessagesID,F.Caption as FolderCaption
 from messages M join folders F
 on M.folderID = F.FolderID
 WHERE BodyPlainText CONTAINS 'Technisat' or Subject like '%Technisat%'
<sql>

returns all found records twice,

while these statements:

<sql>
 select MessagesID from messages
 WHERE BodyPlainText CONTAINS 'Technisat' or Subject like '%Technisat%'
<sql>
<sql>
 select MessagesID,F.Caption as FolderCaption
 from messages M join folders F
 on M.folderID = F.FolderID
 WHERE BodyPlainText CONTAINS 'Technisat'
<sql>
<sql>
 select MessagesID,F.Caption as FolderCaption
 from messages M join folders F
 on M.folderID = F.FolderID
 WHERE Subject like '%Technisat%'
<sql>

work as expected. Maybe I'm blind, but I don't see the reason for that
(there is no outer join).

Regards Uli

Uli,

<< this statement:

<sql>
 select MessagesID,F.Caption as FolderCaption
 from messages M join folders F
 on M.folderID = F.FolderID
 WHERE BodyPlainText CONTAINS 'Technisat' or Subject like '%Technisat%'
<sql>

returns all found records twice, >>

Are you sure that the rows are being returned twice, or that the join is
causing multiple combinations of rows ?  Either way, I'll need to see the
database catalog and tables to be able to tell you what is going on.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,
> Are you sure that the rows are being returned twice, or that the join is
> causing multiple combinations of rows ?  Either way, I'll need to see the
> database catalog and tables to be able to tell you what is going on.

Since the tables are very big, I'll try to create a sample that
reproduces the problem.

Uli

Tim,

> Are you sure that the rows are being returned twice, or that the join is
> causing multiple combinations of rows ?

Never mind. You were right: I didn't post all conditions of the query.
Actually I have to filter out a UserID and I did it like this:

select MessagesID,f.Caption as FolderCaption
from messages m left outer join folders f
on m.folderID = f.FolderID
where (BodyPlainText CONTAINS 'Technisat' or Subject like '%Technisat%')
 and UserID = 21 ;

I assumed that "Where UserID = 21" refers also to the folders table, but
it doesn't.

And Since FolderID is *not* a unique ID, the join produces other
combinations, as you said.

This works now:

select MessagesID,f.Caption as FolderCaption
from messages m left outer join folders f
on m.folderID = f.FolderID where F.BenutzerID = 21 and
and (BodyPlainText CONTAINS 'Technisat' or Subject like '%Technisat%')
and UserID = 21 ;

Regards Uli

Correction of my last posting:

select MessagesID,F.Caption as FolderCaption
from messages M left outer join folders F
on M.folderID = F.FolderID where F.UserID = 21
and (BodyPlainText CONTAINS 'Technisat' or Subject like '%Technisat%')
and M.UserID = 21 ;

Uli

Uli


I did wonder where F.BenutzerID came from but I thought you knew what you were doing

SQL should carry a government health warning!

Roy Lambert

Roy,

> I did wonder where F.BenutzerID came from but I thought you knew what you were doing

I translated "BenutzerID" to "UserID" just to make it clear and forgot a
line.

> SQL should carry a government health warning!



Uli

Uli


Ah. Now so I can call users "Benutzer" - that'll teach them

Roy Lambert

Uli,

<< Never mind. You were right: I didn't post all conditions of the query.
Actually I have to filter out a UserID and I did it like this: >>

Wow, it must be my week - that's the second issue that didn't turn out to my
fault.

I'm glad that you found the problem.

--
Tim Young
Elevate Software
www.elevatesoft.com