CODEWITHSUNDEEP
sqlsql-server
kevorski
kevorski

Reputation: 836

SQL - multi select query returning no results

I have a query where I'm trying to select a Row Number from a table that meets a certain criteria from a separate table.

The current query returns 0 results when I'm expecting 1 number

SELECT 
    RowNum 
FROM
    (SELECT 
         ID, Name, RowNum = ROW_NUMBER() OVER (ORDER BY ID) 
     FROM
         tblEncroachmentTypes) AS temp
WHERE
    temp.Name LIKE (SELECT EN_TYPE 
                    FROM LakeEncroachments 
                    WHERE EN_ID = '0526')

I have created a temp table to try and simplify it, but it still returns no results

select RowNum 
from #temp1 
where #temp1.Name like (select EN_TYPE from LakeEncroachments where EN_ID = '0526')

I'm trying to give as much information as possible, but not sure what else I need.

Upvotes: 1

Views: 216

Answers (2)

Adam Silenko
Adam Silenko

Reputation: 3108

if your subquery give multiple rows, use this query

WITH temp as
  (SELECT ID, Name, ROW_NUMBER() OVER (ORDER BY ID) as RowNum
  FROM tblEncroachmentTypes) AS temp
SELECT temp.RowNum 
FROM LakeEncroachments 
INNER JOIN temp ON temp.Name LIKE REPLACE(REPLACE(LakeEncroachments.EN_TYPE, '-', '% '), ' ', '% ') + '%'
WHERE EN_ID = '0526'

Upvotes: 0

SqlZim
SqlZim

Reputation: 38023

If you need to use like, you might need to add the wildcards:

SELECT RowNum 
from (Select ID, Name, RowNum = ROW_NUMBER() over (order by ID) from tblEncroachmentTypes) as temp
where temp.Name Like '%'+(Select EN_TYPE from LakeEncroachments WHERE EN_ID = '0526')+'%'

reformat looks like this:

select RowNum
from (
 select ID
  , name
  , RowNum = row_number() over (
   order by ID
   )
 from tblEncroachmentTypes
 ) as temp
where temp.name like '%' + (
  select EN_TYPE
  from LakeEncroachments
  where EN_ID = '0526'
  ) + '%'

Also, if your sub query for like returns more than one value, you'll need a different approach.

Upvotes: 1

Related Questions