Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 9 of 9 total |
Unexpected result |
Tue, Jan 19 2010 2:30 PM | Permanent Link |
Uli Becker | 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 |
Wed, Jan 20 2010 8:38 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Thu, Jan 21 2010 1:22 AM | Permanent Link |
Uli Becker | 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 |
Thu, Jan 21 2010 8:14 AM | Permanent Link |
Uli Becker | 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 |
Thu, Jan 21 2010 9:01 AM | Permanent Link |
Uli Becker | 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 |
Thu, Jan 21 2010 11:26 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Thu, Jan 21 2010 12:27 PM | Permanent Link |
Uli Becker | 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 |
Thu, Jan 21 2010 1:26 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Uli
Ah. Now so I can call users "Benutzer" - that'll teach them Roy Lambert |
Thu, Jan 21 2010 5:38 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |