lucasbbs
Rank: #5321
9Views
1Comments
Solved
Error in advanced query Incorrect syntax near the keyword 'exists'
Question
SQL

Hello, there

I have the following problem with my SQL advanced query:

SELECT FirstUser.*, SecondUser.*, {User}.*,{Perfil}.*
FROM ({User}
    Left JOIN {PerfilUser} FirstUser ON ({User}.[Id] = FirstUser.[UserId])),
     {Perfil}  Left JOIN {PerfilUser} SecondUser ON ({Perfil}.[Id] = SecondUser.[PerfilId])
WHERE ({User}.[Is_Active] = 1)
    AND (@PerfilUser_PerfilId <> 0) 
    
    AND ({User}.[Id] not exists 
    (
        SELECT  {PerfilUser}.[UserId], {Perfil}.[Id]  
        FROM {PerfilUser} 
        Left JOIN  {Perfil} ON {PerfilUser}.[PerfilId] = {Perfil}.[Id] 
        WHERE {PerfilUser}.[PerfilId] = {Perfil}.[Id]
    ))

But I keep receiving the error:

Database returned the following error: Error in advanced query GetUsersbyPerfil2: Incorrect syntax near the keyword 'exists'. Incorrect syntax near ')'. 


What am I ding wrong?

0
0
on 2020-12-17
Copy link to comment
João Marques
Rank: #70
Solution

Hi Lucas,


Your EXISTS syntax is wrong.

Please consider the following:


SELECT FirstUser.*, SecondUser.*, {User}.*,{Perfil}.*
FROM ({User}
    Left JOIN {PerfilUser} FirstUser ON ({User}.[Id] = FirstUser.[UserId])),
     {Perfil}  Left JOIN {PerfilUser} SecondUser ON ({Perfil}.[Id] = SecondUser.[PerfilId])
WHERE ({User}.[Is_Active] = 1)
    AND (@PerfilUser_PerfilId <> 0) 
    
    AND not exists 
    (
        SELECT  1  
        FROM {PerfilUser} 
        WHERE {PerfilUser}.[PerfilId] = {Perfil}.[Id] AND {PerfilUser}.[UserId] = {User}.[Id]
    )


I removed the LEFT JOIN you added on the NOT EXISTS branch which was not being used and on the NOT EXISTS the select fields don't matter so it's simpler to make it SELECT 1.


Hope it helps.


Regards,
João

0
0
on 2020-12-17
Copy link to comment