-1

How to use LIKE in SQL Query in where clause, but the value of LIKE will come from in other query? For example:

SELECT Code FROM TABLE1 where Code LIKE '(select top 1 Grade FROM TABLE2 WHERE Age>30)%'

Please anyone can help me

Schadrack Rurangwa
  • 413
  • 12
  • 28
  • Why not join TABLE1 with TABLE 2 ? Can you post the tables' s create ? – Nando Feb 13 '20 at 15:56
  • Sample data and desired results would really help. Why are you comparing a column called `Grade` to one called `Code`? Why are you using `top` with no `order by`? Very mysterious what you want to do. – Gordon Linoff Feb 13 '20 at 16:52

3 Answers3

0

I suppose your tables

create table TABLE1
(
    Code varchar(50)
)

create table TABLE2
(
    Grade varchar(50),
    Age int
)

Adding stuffs

insert TABLE1 values ('AAE')
insert TABLE1 values ('BBM')

insert TABLE2 values ('AAE00001', 22)
insert TABLE2 values ('BBM22501', 31)

Query

select 
Code 
from
TABLE1 inner join TABLE2 on TABLE1.Code = LEFT(TABLE2.Grade, LEN(TABLE1.Code))
where Age > 30

Result

enter image description here

If it's something like that you want, be aware that it's awful. I don't help you, it's a not solution. You have to review your design.

Nando
  • 102
  • 5
0

You must concatenate the result of the subquery with the char '%':

SELECT Code FROM TABLE1 where Code LIKE (select top 1 Grade FROM TABLE2 WHERE Age>30) || '%'

You may change the operator || with + if this is the concatenation operator of your database.
Or with the function concat():

SELECT Code FROM TABLE1 where Code LIKE concat((select top 1 Grade FROM TABLE2 WHERE Age>30), '%')

Note that using top 1 without order by does not guarantee that the result will be what you would expect.

forpas
  • 160,666
  • 10
  • 38
  • 76
0

I have no idea why you would be using top without order by. The normal solution is to use EXISTS:

SELECT t1.Code
FROM TABLE1 t1 
WHERE EXISTS (SELECT 1
              FROM table2 t2
              WHERE t2.Age > 30 AND
                    t1.code LIKE t2.Grade + '%'
             );

This assumes that + is used for string concatenation.

I speculate that this is what you really want -- comparison to all values in table2 rather than just some indeterminate value from a matching row.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I think he wants only records on TABLE1 that compare positively with a single "top" record (whatever record he thinks that means, since there's no `order by`), instead of matching with any record of Age>30. Of course, the question is unclear, so it's difficult to assume anything. – Not a real meerkat Feb 13 '20 at 17:50
  • @CássioRenan . . . I don't think that is the case. I think the OP is using `TOP 1` because otherwise the subquery would return more than one row. It really makes no sense without an `ORDER BY`. – Gordon Linoff Feb 13 '20 at 19:17