CODEWITHSUNDEEP
sqlsql-like
Cilvic
Cilvic

Reputation: 3447

SQL LIKE condition working with result from another query

Does something like this work

SELECT color
FROM rawdata 
WHERE color LIKE (
    SELECT TOP 1 color_condition 
    FROM rules
    WHERE rules_id=1
)

If the color_condition of rule 1 would be B% would this return an entry from the rawdata where color = 'Blue'?

Upvotes: 2

Views: 1725

Answers (2)

onedaywhen
onedaywhen

Reputation: 57093

The answer to the question is "yes" but I wonder if it is your particular SQL construct that is causing your doubt. Personally, I prefer to not see the predicate interrupted with a subquery. See if you find this easier to read:

SELECT D1.color 
  FROM rawdata AS D1
 WHERE EXISTS (
               SELECT * 
                 FROM rules AS R1
                WHERE D1.color LIKE R1.color_condition 
                      AND R1.rules_id = 1
              );

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 454020

Yes.

At least in SQL Server it does and it looks like you are using that from the TOP

WITH rawdata(color) As
(
SELECT 'Blue' UNION ALL SELECT 'Red'
), rules(color_condition,rules_id) AS
(
SELECT 'B%',1
)
SELECT color FROM rawdata 
WHERE color LIKE (SELECT TOP 1 color_condition 
                  FROM rules WHERE rules_id=1)

Returns

color
-----
Blue

Upvotes: 1

Related Questions