how to use LIKE instead of IN with multiple values?

Question

I want to replace In with Like to make the query work.

SELECT  
    1 AS coddit, COD_CAT AS cam_cod, DES_CAT AS cam_desc, 
    LIVELLO_CAT AS livello, COD_CAT_PADRE AS cat_padre,
    COD_L1, COD_L2, COD_L3, COD_L4, COD_L5, COD_L6
FROM
    dbo.CLASS_ART
WHERE
    1=1 
    AND TIPO_CLASS = 16 --B2B
    AND LIVELLO_CAT = '0'
    AND COD_CAT IN (SELECT DISTINCT CAT_MERCE.COD_CAT
                    FROM ART_LIST_PREZZI 
                    INNER JOIN ART_ANA ON ART_LIST_PREZZI.COD_ART = ART_ANA.COD_ART
                    INNER JOIN CAT_MERCE ON ART_ANA.COD_CAT = CAT_MERCE.COD_CAT
                                         AND ART_LIST_PREZZI.COD_LIST = 'EXPORT_002')

The comparison I would like to do with LIKE otherwise the query doesn't work well the subquery returns more than one value and it is correct but if I use Like instead of IN I have this error message:

Query return more than 1 values

you sure thats the query causing that error? as `COD_CAT IN (select DISTINCT CAT_MERCE.COD_CAT from ART_LIST_PREZZI .. )` should work just fine .. — Raymond Nijland, Oct 03 '19 at 09:30
... also you most likely tagged the wrong RDMS .. As `dbo` usage most likely suggests you are using SQL Server (MSSQL) and not MySQL .. — Raymond Nijland, Oct 03 '19 at 09:31
See [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) then we can start giving solid answers.. — Raymond Nijland, Oct 03 '19 at 09:36
check here: https://stackoverflow.com/questions/1865353/combining-like-and-in-for-sql-server — Deepshikha, Oct 03 '19 at 09:37

GMB · Accepted Answer · 2019-10-03T09:39:45.670

Using LIKE against a subquery that returns multiple records won't work. A solution would be to turn the IN condition to an EXISTS condition, like:

and exists (
    select 1
    from  ART_LIST_PREZZI 
    inner join ART_ANA 
        on ART_LIST_PREZZI.COD_ART = ART_ANA.COD_ART
    inner join CAT_MERCE 
        on ART_ANA.COD_CAT = CAT_MERCE.COD_CAT 
        and ART_LIST_PREZZI.COD_LIST = 'EXPORT_002'
    where COD_CAT like '%' + CAT_MERCE.COD_CAT + '%'
)

score 0 · Answer 2 · answered Oct 03 '19 at 10:39

Like has to be compared to a single string, so you need to set all your ids on a single string. You can do that using the for xml clause.

(SELECT DISTINCT CAST(CAT_MERCE.COD_CAT AS VARCHAR(32))
 FROM ART_LIST_PREZZI 
      INNER JOIN ART_ANA ON ART_LIST_PREZZI.COD_ART = ART_ANA.COD_ART
      INNER JOIN CAT_MERCE ON ART_ANA.COD_CAT = CAT_MERCE.COD_CAT
                              AND ART_LIST_PREZZI.COD_LIST = 'EXPORT_002'
 FOR XML PATH(''))

Now I would delimite your ids by commas, so you don't find false positives, and compare it using like.

AND
  (SELECT DISTINCT ',' + CAST(CAT_MERCE.COD_CAT AS VARCHAR(32)) + ','
   FROM ART_LIST_PREZZI 
        INNER JOIN ART_ANA ON ART_LIST_PREZZI.COD_ART = ART_ANA.COD_ART
        INNER JOIN CAT_MERCE ON ART_ANA.COD_CAT = CAT_MERCE.COD_CAT
                                AND ART_LIST_PREZZI.COD_LIST = 'EXPORT_002'
   FOR XML PATH('')) LIKE '%,' + COD_CAT + ',%'

This would work, and you would have changed your IN operator with a LIKE operator, but I don't see the point of it, its performance would be worse than your original query.

score 0 · Answer 3 · answered Oct 03 '19 at 11:12

Use EXISTS:

EXISTS (SELECT 1
        FROM ART_LIST_PREZZI LP JOIN
             ART_ANA A
             ON LP.COD_ART = A.COD_ART JOIN
             CAT_MERCE M
             ON A.COD_CAT = M.COD_CAT AND
                LP.COD_LIST = 'EXPORT_002' AND
                CLASS_ART.COD_CAT LIKE M.COD_CAT
       )

I assume that the logic you actually want uses wildcards:

                CLASS_ART.COD_CAT LIKE CONCAT('%', M.COD_CAT, '%')

If so, it suggests an issue with the data model. Why would two columns with the same name (COD_CAT) need to be joined using LIKE instead of =.

how to use LIKE instead of IN with multiple values?

3 Answers3