Search code examples
sqlsubquerysql-like

SQL - LIKE command using subquery returning multiple rows

I am trying to run a subquery with the same table.

Essentially I have the 1st query:

SELECT workorder
FROM pidata
WHERE equipment LIKE '%ABC%' and dataofinterest LIKE '%EFG%'

And a 2nd query:

SELECT workorder
FROM pidata
WHERE dataofinterest LIKE '%HIJ%'

I want the 2nd query to be filtered by the results of the 1st query.

My best attempt as doing this is the following:

SELECT workorder
FROM pidata
WHERE dataofinterest LIKE '%EFG%' AND workorder LIKE
(SELECT workorder
FROM pidata
WHERE equipment LIKE '%ABC%')

but it has resulted in 'single-row subquery returns more than one row' error message.

Solution

  • As you get multiple workorders you can do following

    SELECT workorder
FROM pidata
WHERE dataofinterest LIKE '%EFG%' AND workorder IN
(SELECT workorder
FROM pidata
WHERE equipment LIKE '%ABC%')

    If the table is very big you can use INNER JOIN

    SELECT workorder
FROM pidata p1 INNER JOIN   (SELECT workorder
FROM pidata
WHERE equipment LIKE '%ABC%') p2 ON p1.workorder = p2.workorder
WHERE dataofinterest LIKE '%EFG%'