SQL Server 2019: Cannot combine two queries, when 1 parses string

There are two tables (table1 and table2).

Table 2 has a column called TaskDescription which has a string with several ‘tags’ embedded in it. I cannot change how that data is stored, as it comes from a a third party. I am able to pull out the taskID

I want to grab all of the information from table 1, where table1.id = the result from the above query. My issue is that the first query returns more than 1 row and I get an error because of this.

SELECT *
FROM table1
WHERE table1.id = (SELECT 
                       CASE WHEN CHARINDEX(' ', a.em) <> 0 
                               THEN SUBSTRING(a.em, 1, CHARINDEX('</taskID>', a.em) - 1)
                            ELSE a.em 
                       END id
                   FROM 
                       (SELECT 
                            SUBSTRING(TaskDescription, CHARINDEX('<taskid>', TaskDescription) + 8, LEN(TaskDescription)) em
                        FROM table2
                        WHERE TaskDescription LIKE '%schemaupdate%') a
        )

Answer

When the results of a limiting query that we are using for a lookup has more than 1 record, but we want to use a scalar expression (single value result) then we have 4 options:

1. Check your limiting query on its own, does it return the data you are expecting?
   • Were you expecting a single result?
2. If all the values are the same in the lookup then DISTINCT can be satisfactory, it is lower performance than the next option, but it allows you to still detect errors later when the query returns different values.
3. If you do not care about the specific value, or you know that they will always be the same value and you do not need to validate it, you can return TOP 1.
   • make sure you set an appropriate ORDER BY when using TOP
4. Change your query to be set based, either by changing the = to an IN operator

Distinct:

SELECT *
FROM table1
WHERE table1.id = (SELECT DISTINCT
                       CASE WHEN CHARINDEX(' ', a.em) <> 0 
                               THEN SUBSTRING(a.em, 1, CHARINDEX('</taskID>', a.em) - 1)
                            ELSE a.em 
                       END id
                   FROM 
                       (SELECT 
                            SUBSTRING(TaskDescription, CHARINDEX('<taskid>', TaskDescription) + 8, LEN(TaskDescription)) em
                        FROM table2
                        WHERE TaskDescription LIKE '%schemaupdate%') a
        )

TOP 1:

SELECT *
FROM table1
WHERE table1.id = (SELECT TOP 1
                       CASE WHEN CHARINDEX(' ', a.em) <> 0 
                               THEN SUBSTRING(a.em, 1, CHARINDEX('</taskID>', a.em) - 1)
                            ELSE a.em 
                       END id
                   FROM 
                       (SELECT 
                            SUBSTRING(TaskDescription, CHARINDEX('<taskid>', TaskDescription) + 8, LEN(TaskDescription)) em
                        FROM table2
                        WHERE TaskDescription LIKE '%schemaupdate%') a
        )

Set Based (IN):

SELECT *
FROM table1
WHERE table1.id IN (SELECT 
                       CASE WHEN CHARINDEX(' ', a.em) <> 0 
                               THEN SUBSTRING(a.em, 1, CHARINDEX('</taskID>', a.em) - 1)
                            ELSE a.em 
                       END id
                   FROM 
                       (SELECT 
                            SUBSTRING(TaskDescription, CHARINDEX('<taskid>', TaskDescription) + 8, LEN(TaskDescription)) em
                        FROM table2
                        WHERE TaskDescription LIKE '%schemaupdate%') a
        )