Verifying with Advanced SQL Query

Verifying with Advanced SQL Query

I am new to Outsystems and advanced SQL queries. I was trying to create a sql query which will generate a list of projects to which no user is assigned. It is not throwing any error but the the lis is empty. I cannot test by creating a new project now without assigning an user. Just want to verify if my below query is correct.

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

FROM {Project}

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

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

I would change your INNER JOIN to:

LEFT JOIN {ProjectParticipant} ON {Project}.[Id]={ProjectParticipant}.[ProjectId]
WHERE {ProjectParticipant}.[UserId] IS NULL
Can you explain why do I have to change the inner join.

Because I need the Projects only without user assigned.
If I make Left Outer Join it might pull project with or without user assigned right?
Am I understanding it right?

Yes vdha.

We are saying we want results from table Projects (left join) where no Project user is assigned (ProjectUserId is null).
It includes results from Projects by excluding Projects that have a User associated through ProjectParticipant. (in other words: output only Projects that have no ProjectParticipant.UserId)
Thanks a lot Tiago.

I have used this
LEFT JOIN {ProjectParticipant} ON {Project}.[Id]={ProjectParticipant}.[ProjectId]
WHERE {ProjectParticipant}.[UserId] IS NULL

still it shows no projects. I think there is no projects without User in the system. Thanks a lot for explaining

Hi vdha,

You can do this with an aggregate. Left join Projects with Participants, group by projects and count participants and set a group filter to return only projects with 0 in the count of participants.
Do I have to Count User Id in Project Participant?
You can count any column in the participant entity since you are grouping by project.
Is there any drawback in using the above sql query?
The only I can think of is that it will be harder to change in the long run compared with aggregates.
Thanks Andre for expolain well.