-2

I have query:

SELECT p.[id], p.[page_file], p.[page_header], p.[page_icon] 

FROM [dbo].[setup_pages] AS p 

WHERE CONVERT(nvarchar(100),p.[id]) IN ( SELECT l.[access] FROM [dbo].[licenses] AS l )

It does not return error and no data are returned.

SELECT l.[access] FROM [dbo].[licenses] AS l returns 3 lines:
1) 8,9,15,4,13,11,5,14,12,16
2) 20,19
3) 20,8,9,15,4,13,11,5,14,12,16

and if i run query like this:

SELECT p.[id], p.[page_file], p.[page_header], p.[page_icon] 

FROM [dbo].[setup_pages] AS p 

WHERE CONVERT(nvarchar(100),p.[id]) IN ( 8,9,15,4,13,11,5,14,12,16 )

It does work. .. Where is my issue?

Ingus
  • 59
  • 8

1 Answers1

1

From your description, it appears that the access column in the licenses table is a character column containing a string - whose content is a comma delimited list of numbers.

So, if we extract that result of SELECT l.[access] FROM [dbo].[licenses] AS l and pretend we are putting it in the IN directly, it wouldn't look like your second query - instead it would look like this:

SELECT p.[id], p.[page_file], p.[page_header], p.[page_icon] 
FROM [dbo].[setup_pages] AS p 
WHERE CONVERT(nvarchar(100),p.[id]) IN (
  '8,9,15,4,13,11,5,14,12,16',
  '20,19',
  '20,8,9,15,4,13,11,5,14,12,16'
)

And now it is very clear why the first query fails - a specific id from setup_pages will never match any of the strings in licenses.access. You may want to take a look at your database model and maybe replace the access column with a "m:n" mapping table.

BTW: it looks like you are trying to use CONVERT() to cast the id field to a character string, but that would still not make it match to a string containing multiple numbers, and it also unneeded - according to the SQL standard the database server is required to do such casting automatically when trivial, which is the reason why your second query actually works even though it compares the id value in the form of a string to a list of numbers that are not formatted as strings.

Guss
  • 2,670
  • 5
  • 34
  • 59
  • Yeah i noticed what you now showed me in your example query! About convert it gave me error if i did not used it (`Conversion failed when converting the nvarchar value '8,9,15,4,13,11,5,14,12,16' to data type int.`) – Ingus May 18 '20 at 13:13
  • 1
    Yea, that make sense - because the server tries to match the value types, but "1,2,3,4" cannot be converted to an integer. That should have been an indication as to what is wrong. BTW - other SQL implementations might just convert the `id` integer value to varchar for you automatically and not show you the error, so you should thank Microsoft for making the server dumb ;-) – Guss May 18 '20 at 13:29
  • Ok xD thanks for your help and time! – Ingus May 18 '20 at 13:33