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'
)

Answer

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'