subquery or where clause? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

subquery or where clause?

Author

Topic

abennen
Starting Member

1 Post

Posted - 2008-07-14 : 10:45:12

Hello,

I have 2 tables (tblFiles and tblFilesToTask).
tblFiles
FileId
ProjectId
FileName

tblFilesToTask
FileId
TaskId
FileName

Files from tblFiles can exist more than once in tblFilesToTask.
I would like to have a list of files with ProjectId x where the TaskId is not the current TaskId. The files may exist in tblFilesToTask but may not have the TaskId.
If I test my query it works, but if I use it in a ASP.Net page is doesn't. I guess there's something wrong with my query, but I cannot figure out what.


        SelectCommand="SELECT tblFiles.FileId, tblFiles.FileName, FilesPerTask.FileId AS FileToTask, FilesPerTask.TaskId, tblFiles.ProjectId FROM tblFiles LEFT OUTER JOIN FilesPerTask ON tblFiles.FileId = FilesPerTask.FileId WHERE (FilesPerTask.TaskId <> @TaskId) OR (FilesPerTask.TaskId IS NULL) AND (tblFiles.ProjectId = @ProjectId)">
               <SelectParameters>
                   <asp:SessionParameter Name="TaskId" SessionField="TaskId" />
        <asp:SessionParameter Name="ProjectId" SessionField="ProjectId" Type="Int32" Direction="Input"/>
        </SelectParameters>

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-14 : 10:51:00

[code]SELECT tblFiles.FileId,
tblFiles.FileName,
FilesPerTask.FileId AS FileToTask,
FilesPerTask.TaskId,
tblFiles.ProjectId
FROM tblFiles
LEFT JOIN FilesPerTask ON FilesPerTask.FileId = tblFiles.FileId
AND FilesPerTask.TaskId <> @TaskId
WHERE FilesPerTask.TaskId IS NULL
AND tblFiles.ProjectId = @ProjectId[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources