Advanced SQL Queries in Outsystems

Question

I have to select list of projects where an user doesnot have access to in the given organization.

I tried with this query

SELECT {Project}.[Number],{Project}.[Name]

FROM {Project}

INNER JOIN {ProjectParticipant} ON {Project}.[Id]={ProjectParticipant}.[ProjectId]

WHERE {Project}.[Tenant_Id]=@TenantId AND {ProjectParticipant}.[UserId] <> @UserId

GROUP BY {Project}.[Number],
         {Project}.[Name]

ORDER BY {Project}.[Number]

But here it lists all the Projects within the given organization. I am missing something basic. Tried using left outer join too but no use. Help me out

Answer 1

It looks like you want to get all rows in the Project table for which there is no corresponding row in the ProjectParticipant table for the given userid.

This should do the trick:

SELECT {Project}.[Number],{Project}.[Name]
FROM {Project}
WHERE {Project}.[Tenant_Id]=@TenantId
AND NOT EXISTS (
    SELECT * FROM {ProjectParticipant} 
    WHERE {Project}.[Id]={ProjectParticipant}.[ProjectId]
    AND {ProjectParticipant}.[UserId] = @UserId
)
ORDER BY {Project}.[Number]