tags:

views:

3447

answers:

3

I'm trying to figure out how to exclude items from a select statement from table A using an exclusion list from table B. The catch is that I'm excluding based on the prefix of a field.

So a field value maybe "FORD Muffler" and to exclude it from a basic query I would do:

SELECT FieldName 
FROM TableName 
WHERE UPPER(ColumnName) NOT LIKE 'FORD%'

But to use a list of values to exclude from a different tabel I would use a Subquery like:

SELECT FieldName 
FROM TableName 
WHERE UPPER(ColumnName) NOT IN (Select FieldName2 FROM TableName2)

The problem is that it only excludes exact matches and not LIKE or Wildcards (%).

How can I accomplish this task? Redesigning the table isn't an option as it is an existing table in use.

EDIT: Sorry I am using SQL Server (2005).

+8  A: 

I think this will do it:

SELECT FieldName
FROM TableName
LEFT JOIN TableName2 ON UPPER(ColumnName) LIKE TableName2.FieldName2 + '%'
WHERE TableName2.FieldName2 IS NULL
Joel Coehoorn
Why do you have WHERE TableName2.FieldName2 IS NULL? This would seem to defeat the purpose of the join.
recursive
It's there because he only wants records that do NOT have matches for the join. Because it's a left outer join records from the first table are still available when applying the where filter. Records from the 2nd table just have null values.
Joel Coehoorn
Put another way, an INNER join would return exactly the opposite of what he wants: records where the 1st table IS like the 2nd. An OUTER join with no where clause would return BOTH: records that do AND do not match. Then add this where clause and we're back to only records that do NOT match.
Joel Coehoorn
+4  A: 

Dunno how efficient this would be, but it should work:

SELECT FieldName 
FROM TableName t1
WHERE NOT EXISTS (
    SELECT *
    FROM TableName2 t2
    WHERE t1.FieldName LIKE t2.FieldName2 + '%'
)
Matt Hamilton
I would alter this to "select 1" rather than "select *" as there's no point retrieving all the fields as you don't use them.
Garry Shutler
Join is generally faster, but of course you always need to profile to know for sure.
Joel Coehoorn
The query optimizer will probably optimize it away anyway.
recursive
@Garry I always use "1" myself in "EXISTS" subqueries, but thought it might confuse folks in this answer. I'm not convinced that it makes a difference with more advanced SQL engines anyway.
Matt Hamilton
@recursive it should, but it doesn't always. In this case, the rewrite is simple, but they can get fairly hairy since any arbitrary query can be inside "exists", and it's not clear how to cost the exists list vs the join. So the optimizer might not see this. Especially the mysql optimizer...
SquareCog
+2  A: 
SELECT * FROM table_A 
LEFT OUTER JOIN table_B 
    ON (locate(table_b.column, UPPER(table_a.column)) == 1)
WHERE table_b.column IS NULL
SquareCog