Posted by Razvan Socol on 10/10/05 20:19

Hello, Rob

Instead of this:

SELECT * FROM employees
WHERE LastName LIKE 'C%'
OR LastName LIKE 'D%'
OR LastName LIKE 'F%'

you can use:

SELECT e.* FROM employees e INNER JOIN (
SELECT 'C' AS Prefix
UNION ALL SELECT 'D'
UNION ALL SELECT 'F'
) x ON e.LastName LIKE x.Prefix+'%'

or:

SELECT * FROM employees e
WHERE EXISTS (
SELECT * FROM (
SELECT 'C' AS Prefix
UNION ALL SELECT 'D'
UNION ALL SELECT 'F'
) x WHERE e.LastName LIKE x.Prefix+'%'
)

The last query is more correct than the previous (in case that two
prefixes could match the same name).

Of course, it's better to use a temporary table to store all the
requested prefixes.

Razvan

• Next in forum: Re: selecting only the last record in joined table
• Prev in forum: One-One Relationship
• Thread view: Re: LIKE and IN can I combine

[Reply to this message]