SQL As with a subquery

Question

How can I do this job?

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

The inner subquery returns a list of rows, such as' '' test '' another '', and I want to select all the elements from the element table, where item_name is similar to the returned values ​​of the subqueries. I need to have wild cards.

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

Answer 1

You can use 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 || '%' 

Answer 2

If you don't want to worry about duplicates and don't care which one matches, then switch to using exists :

 select i.* from item i where exists (select 1 from equipment_type where i.item_name like '%'||equipment_type||'%' )