Como criar uma coluna (bit) com a informação se o campo existe numa tabela

Question

É o Seguinte tenho duas tabalas 'Roles' e 'UsersInRoles' respectivamente:

     |   RoleID   |    RoleName   |                                               |   RoleID   |   UserID   |

     |       1        |    Admin          |                                              |       1        |        1        |

     |       2        |     Teste1        |                                              |        3        |        2       |

     |       3        |      Teste2       |                                              |        3        |         1       |

     |       4        |      Teste3       | 

 

Gostaria de obter atraves de um SELECT uma tabela em que numa coluna apresente todos os 'RoleName' existentes na tabela 'Roles', e em outra "0" ou "1" Conforme esse 'RoleID' exista na tabela 'UsersInRoles' para um determinado 'UserID'

Exemplo Para UserID = 1

     |   Name            |   Existe    |

     |    Admin          |       1        |

     |     Teste1        |        0       |

     |      Teste2       |        1       |

     |      Teste3       |        0       |

 

Exemplo Para UserID = 2

     |   Name            |   Existe    |

     |    Admin          |       0        |

     |     Teste1        |        0       |

     |      Teste2       |        1       |

     |      Teste3       |        0       |

 

Consigo em MS Access usando o seguinte SQL

"SELECT Roles.RoleName AS Name, Roles.RoleID IN (SELECT UsersInRoles.RoleID
FROM UsersInRoles WHERE UsersInRoles.UserID=[?UserID]) AS Existe
FROM Roles;"

 

mas ao tentar criar uma Stored Procedure no SQL obtenho um ERRO:

"incorrect syntax near de keyword IN"

"incorrect syntax near de keyword FOR"

Se alguem me poder ajudar, muito grato.

Answer 1

RCFernandes,

Você poderia utilizar Inner Join, veja se este exemplo pode te ajudar a entender melhor.

SELECT Rol.RoleName AS Name,

                 Ro.RoleID 

                 From Roles Ro Inner Join UsersInRoles UIR

                                             On Ro.RoleID = UIR.UsersInsRoles

Trata-se de uma simples idéia, baseado na sua necessário, sem levar em consideração qual regra de negócio ou estrutura dos campos utilizados para o relacionamento entre suas tables.

Answer 2

Obrigado pela ajuda, mas ainda não resolve o meu problema.

Desse modo apenas me seriam devolvidos as campos da tabela 'Roles' presentes em 'UsersInRoles', e não obteria a coluna que me indicasse se esse campo se encontrava na tabela 'UsersInRoles'

O que eu precisava era que todos os campos 'RoleName' da tabela 'Roles' me sejam devolvidos e noutra coluna obter a informação ( TRUE, FALSE)  se para cada um deles existe im registo na tabela 'UsersInRoles'.

 

Com o MS Access eu consigo usando o SQL mostrado anteriormente o problema é que no SQL Server não me aceita o 'IN' dentro dos campos SELECT.

Answer 3

RCFernandes,

A questão é a seguinte, o SQL Server trabalha com IN no Select, mas você esta fazendo um SubQuery que esta retornando mais de uma linha por isso esta dando este erro.

Answer 4

 

É verdade a minha SubQuery retorna mais de uma linha.

(SELECT UsersInRoles.RoleID
FROM UsersInRoles WHERE UsersInRoles.UserID=[?UserID] )

por isso eu tenho o "Roles.RoleID IN " antes, a fim de obter TRUE ou FALSE conforme exista ou não um mesmo Roles.RoleID na SubQuery

Segundo o Help:

Utilize o predicado IN para obter apenas os registos da consulta principal para os quais existe um registo na subconsulta com um valor igual   "expressão [NOT] IN (instruçãosql)"

 

Mas o que mais me admira é que no MS Access retorna a tabela esperada.

De qualquer modo se alterar a SubQuery de modo a que apenas retorne uma linha continua a dar o mesmo erro:

Ex:

"SELECT Roles.RoleName AS Name, Roles.RoleID IN (SELECT UsersInRoles.RoleID
FROM UsersInRoles WHERE UsersInRoles.UserID=[?UserID] AND UsersInRoles.RoleID = '1') AS Existe
FROM Roles;"

 

De qualquer modo não tem de ser com o IN apenas foi o unico metodo que me ocoreu, qualque outra maneira que me resolva o problema é valida.

Obrigado, qualquer sujestão é bem vinda.

Em relação ás relações entra as tabelas á apenas uma           Roles.RileID - UsersInRoles.RoleID