Thanks. That code works. Now I'm extending the codes since the actual goal I
want to achieve is much more complicated than the scenario i gave.
----------------------------------------------------------------------------
---- -----------------------------------------------------------------------
---------
[quoted text, click to view] "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:pl93k0dbqn5aodc84349bufk2cbmfbh01e@4ax.com...
> On Fri, 10 Sep 2004 20:03:44 +0800, Razak wrote:
>
> >I have 3 tables :-
> >1) Files - fields: FileID, Filename
> >2) Projects - fields: ProjectID, ProjectName
> >3) FileProjectLinks - fields: FileID, ProjectID
> >
> >Files table keeps the list of all files, whereas Projects table keeps the
> >list of all projects. A file may have been used in one or more projects,
or
> >may not have been used in any project at all. So to keep the relations
> >between files and projects, I created the table FileProjectLinks to keep
the
> >list of references between files and projects. Files that have not been
used
> >in any projects will not have any entry in the FileProjectLinks, and
files
> >that have been used in 1 or more projects, will have 1 or more entries in
> >FileProjectLinks, depending on the number of projects.
> >
> >The question is, how to write the SQL string to list files that have not
> >been used in a specified Project ID. To make this clear:
> >
> >I have a project named 'Math Project' with an ID of 10 and 'Science
Project'
> >with an ID of 11.
> >I have a file of an ID 50 which linked to both Math Project and Science
> >Project, therefore in FileProjectLinks, there will be 2 entries as
follows:-
> >FileID ProjectID
> >==== =======
> > 50 10
> > 50 11
> >
> >To list files that are not used in Math Projects, I tried :-
> >SELECT Files.FileID
> >FROM Files
> >LEFT JOIN FileProjectLinks
> >ON Files.FileID=FileProjectLinks.FileID
> >GROUP BY Files.FileID, FileProjectLinks.ProjectID
> >HAVING FileProjectLinks.ProjectID IS NULL OR FileProjectLinks.ProjectID
<>
> >10
> >
> >The above SQL string will list all files that are not used in any
projects
> >by having the line 'FileProjectLinks.ProjectID IS NULL' but it will still
> >list file ID 50 since it has the second entry in FileProjectLinks above.
How
> >to make the server not to list the file at all?
> >
> >Thanks in advance.
> >
>
> Hi Razak,
>
> How about this:
>
> SELECT FileID
> FROM Files AS F
> WHERE NOT EXISTS
> (SELECT *
> FROM FileProjectLinks AS FP
> WHERE FP.FileID = F.FileID
> AND FP.ProjectID = 10)
>
> or
>
> SELECT Files.FileID
> FROM Files AS F
> LEFT OUTER JOIN FileProjectLinks AS FP
> ON FP.FileID = F.FileID
> AND FP.ProjectID = 10
> WHERE FP.ProjectID IS NULL
>
> (both queries untested)
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)
