SQL Like with a subquery

sql subquery wildcard
jordan picture jordan · Jun 18, 2013 · Viewed 49.3k times · Source

How can i make this work?

SELECT * 
FROM   item 
WHERE  item_name LIKE '%' 
                      || (SELECT equipment_type 
                          FROM   equipment_type 
                          GROUP  BY equipment_type) 
                      || '%'

The inner sub query returns a list of strings like 'The' 'test' 'another' and i want to select all items from the item table where the item_name is similar to the sub queries return values. I need to have the wild cards.

Is there an alternative where i can use wildcards but use the IN sql command instead?

Answer

Lamak picture Lamak · Jun 18, 2013

You can use an INNER JOIN:

SELECT I.* 
FROM item I
INNER JOIN (SELECT equipment_type 
            FROM equipment_type 
            GROUP BY equipment_type) E
    ON I.item_name LIKE '%' || E.equipment_type || '%'

Related questions

Nested select statement in SQL Server

Why doesn't the following work? SELECT name FROM (SELECT name FROM agentinformation) I guess my understanding of SQL is wrong, because I would have thought this would return the same thing as SELECT name FROM agentinformation Doesn't the inner select …

Read More
Join vs. sub-query

I am an old-school MySQL user and have always preferred JOIN over sub-query. But nowadays everyone uses sub-query, and I hate it; I don't know why. I lack the theoretical knowledge to judge for myself if there is any difference. …

Read More
updating table rows in postgres using subquery

Using postgres 8.4, My goal is to update existing table: CREATE TABLE public.dummy ( address_id SERIAL, addr1 character(40), addr2 character(40), city character(25), state character(2), zip character(5), customer boolean, supplier boolean, partner boolean ) WITH ( OIDS=FALSE ); Initially i tested my query …

Read More