StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POQuerying delimited column value in SQL Server
text
Body
copied!<p>I'm wondering why is this code not returning any result </p> <pre><code> SELECT A.PermissionGroupID, A.ApplicationID, CONVERT(nvarchar(5),A.PermissionID) AS Permission, A.PermissionName, B.PermissionGroupName, C.ApplicationName FROM Permission AS A JOIN PermissionGroup AS B ON A.PermissionGroupID = B.PermissionGroupID LEFT JOIN Application AS C ON A.ApplicationID = C.ApplicationID WHERE (A.Active = 1) AND (CONVERT(nvarchar(2),A.PermissionID) IN (SELECT Permissions FROM UserPermissions WHERE UserID = 1)) </code></pre> <p>But this one is working</p> <pre><code> SELECT A.PermissionGroupID, A.ApplicationID, CONVERT(nvarchar(5),A.PermissionID) AS Permission, A.PermissionName, B.PermissionGroupName, C.ApplicationName FROM Permission AS A JOIN PermissionGroup AS B ON A.PermissionGroupID = B.PermissionGroupID LEFT JOIN Application AS C ON A.ApplicationID = C.ApplicationID WHERE (A.Active = 1) AND (CONVERT(nvarchar(2), A.PermissionID) IN ('5','6','7','8')) </code></pre> <p>Here's the content of my Permission table</p> <pre><code> Permission -------------------------------- Permission Permission Application Permission ID Group ID Name 4 1 1 VISA_APPLICATION_DELETE 5 1 1 VISA_APPLICATION_PRINT 6 4 10 APPLICATION_ADD 7 4 10 APPLICATION_EDIT 8 4 10 APPLICATION_DELETE 9 4 10 APPLICATION_VIEW </code></pre> <p>Here's the content of my UserPermissions Table</p> <pre><code>UserPermissions -------------------------- UserPermission UserID Permissions ID 2 1 5,6,7,8 -> I tried to change it manually to this format ('5','6','7','8') but to no avail. </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload