Advanced SQL Queries

Advanced SQL Queries

  
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}

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

WHERE {ProjectParticipant}.[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 inner join too but no use. Help me out
I am stuck with this.Please any help is really appreciated. Thanks
Not sure what is the mistake
Hi vdha,

Try it this way. Why do you have Tenant_Id in the query?

SELECT {Project}.[Number],{Project}.[Name]
FROM {Project}
WHERE
not exists (select 1 from FROM {ProjectParticipant}
WHERE 
   {Project}.[Id]={ProjectParticipant}.[ProjectId]
AND {ProjectParticipant}.[UserId] = @UserId)        
ORDER BY {Project}.[Number]
I was using Tenant_Id becaues I have to list the project to which an user doesnot have access to a given organization.

I tried the code exactly like you suggested without Tenant_Id , it is not listing any projects.
For any user it is showing no projects to show.
This will not be the best sql, but it's easy to understand
basically, get all projects where the user does particpate in, those id's you want to exclude..

SELECT {Project}.[Number],{Project}.[Name]
FROM {Project}
WHERE {Project}.[ID] NOT IN
(
SELECT DISTINCT {ProjectParticipant}.[ProjectId] 
FROM {ProjectParticipant}
WHERE {ProjectParticipant}.[UserId] = @UserId
)

vdha,

My SQL query had an error it was using <> instead of = to compare the UserId. Are you using multi-tenancy in this application?
Yes. we are using multi-tenancy. I used = for comparing UserId but it is not working. Shows no user to show.

But this  code by J is working,
SELECT {Project}.[Number],{Project}.[Name]
FROM {Project}
WHERE {Project}.[ID] NOT IN
(
SELECT DISTINCT {ProjectParticipant}.[ProjectId] 
FROM {ProjectParticipant}
WHERE {ProjectParticipant}.[UserId] = @UserId
)

Thank you guys. I think I have long way to learn lot of things