Issue using the Advanced Query

Issue using the Advanced Query

  
 

I have a table Projects which has the following fields

ID| Number  | Name    | CreatedOn  | CreatedBy 
----------------------------------------------

Other table is CLDProject has the following fields

ID| CLDId  | ProjectId | CreatedOn  | CreatedBy 
-----------------------------------------

Here I have to select the list of all Projects where the CLDProject.ProjectId is not assigned for the particular CLDId

I tried
SELECT {Project}.* FROM {Project} WHERE NOT EXISTS ( SELECT 1 FROM {CLDProjects} WHERE {CLDProjects}.[ProjectId] = {Project}.[Id] AND {CLDProjects}.[CLDId] =@CLDId)

 


The Problem is it shows all the Projects but I want to select projects where the CLDProject.ProjectId is not assigned for the particular CLDId. I am new to queries any help with this is greatly appreciated.
Hi,

To be honest, this is basic SQL. So if you have more issues with that, I suggest to take a basic course SQL.
It doesn't matter what kind of SQL, they all work roughly the same
anyways:

SELECT {Project}.* FROM {Project}
WHERE {Project}.[Id] NOT IN
(SELECT {CLDProjects}.[ProjectId]
FROM {CLDProjects}
WHERE {CLDProject}.[CLDId] = @CLDid)

Hi v,

Maybe it's more visible (simpler) in an aggregate?
You only have to set the following:
Sources: Project & CLDProject
Join Condition: Project.Id=CLDProject.ProjectId (With or Without)
Filter: CLDProject.Id = NullIdentifier()
Filter: CLDProject.CLDid = CLDId (Input parameter)

Hope it helps!