CODEWITHSUNDEEP
sqlsql-serversql-like
SomethingCool
SomethingCool

Reputation: 303

Use Like operator in Select query with results from another Select query

I have a query with inner joins that returns 4-5 rows - the query: 

select Table1.valstring Prefix
from TestDB.dbo.SomeCompany PC
    INNER JOIN TestDB.dbo.CMCompany CMC ON PC.companyuid = CMC.companyuid
    LEFT OUTER JOIN TestDB.dbo.ATRIBUTE Table1 ON Table1.Contract = CMC.contract AND Table1.attribute = 'SomeThing'
    LEFT OUTER JOIN TestDB.dbo.ATRIBUTE TheRealPrefix ON TheRealPrefix.Contract = CMC.contract AND TheRealPrefix.attribute = 'SomeOtherPrefix'
where secretCode = 'Mistery'

I would like to feed this query into the another select query that uses like operator with the results from the already described query.

So something like this: 

    select from taskTable where task like ('%' + select Table1.valstring Prefix
    from TestDB.dbo.SomeCompany PC
        INNER JOIN TestDB.dbo.CMCompany CMC ON PC.companyuid = CMC.companyuid
        LEFT OUTER JOIN TestDB.dbo.ATRIBUTE Table1 ON Table1.Contract = CMC.contract AND Table1.attribute = 'SomeThing'
        LEFT OUTER JOIN TestDB.dbo.ATRIBUTE TheRealPrefix ON TheRealPrefix.Contract = CMC.contract AND TheRealPrefix.attribute = 'SomeOtherPrefix'
    where secretCode = 'Mistery'
)

Upvotes: 0

Views: 97

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

If you require a match, then you don't require left joins. So remove them. It is unclear where secretCode comes from; that should be explicit.

The last join on attribute is useless -- not used for filtering. And secretCode does not come from that table (you would get an error because the reference is not qualified).

So, I think this does what you want:

SELECT tt.*
FROM TestDB.dbo.SomeCompany PC JOIN
     TestDB.dbo.CMCompany CMC 
     ON PC.companyuid = CMC.companyuid JOIN
     TestDB.dbo.ATRIBUTE a
     ON a.Contract = CMC.contract AND a.attribute = 'SomeThing' JOIN
     taskTable tt
     ON task like CONCAT('%', a.valstring)
WHERE secretCode = 'Mistery'

Upvotes: 1

Scrappy Coco
Scrappy Coco

Reputation: 564

Try to use this query:

select *
from TestDB.dbo.SomeCompany PC
    INNER JOIN TestDB.dbo.CMCompany CMC ON PC.companyuid = CMC.companyuid
    LEFT JOIN TestDB.dbo.ATRIBUTE Table1 ON Table1.Contract = CMC.contract AND Table1.attribute = 'SomeThing'
    LEFT JOIN TestDB.dbo.ATRIBUTE TheRealPrefix ON TheRealPrefix.Contract = CMC.contract AND TheRealPrefix.attribute = 'SomeOtherPrefix'
    LEFT JOIN taskTable ON taskTable.task LIKE '%' + Table1.valstring
 where secretCode = 'Mistery'

But keep in mind, if you have many rows in your DB, search by '%' + Table1.valstring is bad practice.

Upvotes: 0

Related Questions