CODEWITHSUNDEEP
sqlsql-server
Janko
Janko

Reputation: 23

How to combine LIKE and IN in SQL?

I need to find out if specific good is a commodity or a service.

I have two tables of importance, GoodsClass and PubVars.

GoodsClass has a hierarchy of goods which are defined by it's SortKey, eg. goods class with SortKey value of 01 is a parent to goods class with SortKey value of 0101 and all other goods classes that have their SortKey start with 01.

PubVars tells me which of these GoodsClass is a commodity class, and which is a service class.

I've tried this query, but the subquery returns more than one value so it cannot work like this:

SELECT GoodsClassId 
FROM GoodsClass 
WHERE SortKey LIKE (SELECT SortKey FROM GoodsClass 
                    WHERE GoodsClassId IN (SELECT VarId 
                                           FROM PubVars 
                                           WHERE VarName IN ('Commodity', 'Service'))) + '%'

How can I rewrite this query so that I can retrieve the GoodsClassId's I need?

I don't really know how to give nice sample data, but let's put it like this... PubVars has IDs of 216 for 'Commodity', and 203 for 'Service'.

enter image description here enter image description here

So, I need my query to return all IDs on both of the pictures (IDs 203-212 and 216-221).

Upvotes: 0

Views: 110

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269523

You can rewrite your current logic using EXISTS:

SELECT gc.GoodsClassId
FROM GoodsClass gc
WHERE EXISTS (SELECT gc2.SortKey
              FROM GoodsClass gc2
              WHERE gc2.GoodsClassId IN (SELECT pv.VarId
                                         FROM PubVars pv
                                         WHERE pv.VarName IN ('Commodity', 'Service')
                                        ) AND
                    gc.SortKey LIKE gc2.SortKey + '%'
             );

That said, I think there are probably better ways to write this logic. But without sample data, desired results, and a clear explanation of the logic, it is hard to make suggestions. You can ask a new question, if you want to investigate that.

Upvotes: 1

Related Questions