Return not matching rows using LIKE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Return not matching rows using LIKE

Author

Topic

MyronCope
Starting Member

46 Posts

Posted - 2010-11-15 : 09:06:34

using sql server 2005.

I have 2 tables that I need to query and return all OrderNumbers that do not have a folder created containing the orderNumber. I cannot do an "=" join because the folder names will vary.
EX: folderURL(column) "folderBKX00998jmk"
where 000998 would be the orderNumber.

The tables and relevant columns are:
1. Folders
folderURL

2. Orders
OrderNum

The folderURL will contain an order Number, at first I tried this:

SELECT orderNum FROM FOLDERS, Orders
WHERE Folders.folderURL NOT LIKE '%' + orderNum + '%'

however this returned all the orderNums because every orderNum would have at least one row in the folders table where there was not a match on the folderURL field.

so to sum it up I want to return all orderNums that have no matches in the Folders.FolderURL field, does anyone know how to do this?

thanks

MC

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-15 : 09:32:25

How about returning the matches and then filtering from table using not in .

MyronCope
Starting Member

46 Posts

Posted - 2010-11-15 : 10:21:30

quote:
Originally posted by pk_bohra

How about returning the matches and then filtering from table using not in .

good idea, do you mean something like this:

<SQL>

SELECT orderNUM FROM ORDERS
WHERE LTRIM(orderNum) NOT IN
(
select LTRIM(orderNum) FROM ORDERS, FOLDERS
WHERE folderURL
LIKE '%'+ LTRIM(orderNum) + '%'
)

</SQL>

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-11-15 : 10:35:09

[code]
--either
SELECT *
FROM Orders O
WHERE NOT EXISTS
(
SELECT *
FROM Folders F
WHERE F.FolderURL LIKE '%' + LTRIM(O.orderNum) + '%'
);
-- or
SELECT *
FROM Orders O
LEFT JOIN Folders F
ON F.FolderURL LIKE '%' + LTRIM(O.orderNum) + '%'
WHERE F.FolderURL IS NULL;
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources