CODEWITHSUNDEEP
sqlsql-serversubquery
lucamontessori
lucamontessori

Reputation: 77

how to use LIKE instead of IN with multiple values?

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

Upvotes: 0

Views: 369

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

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 =.

Upvotes: 0

Marc Guillot
Marc Guillot

Reputation: 6455

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.

Upvotes: 0

GMB
GMB

Reputation: 222422

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 + '%'
)

Upvotes: 3

Related Questions