Search code examples
mysqloutsystems

Issue with Advanced Query not returning desired results

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)

enter image description here

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.

Solution

  • Have you tried something like

    SELECT {Project}.*
FROM {Project}
WHERE {Project}.ID NOT IN (
    SELECT DISTINCT {CLDProject}.[ProjectId]
    FROM {CLDProject}
    WHERE {CLDProject}.[CLDId] = @CLDId
    )

    This is how it works

    1. Find all projects associated with a given CLDID (inner query)

    2. Find the projects that are not on that list (outer query)