Interesting Database Query Question!! -- asp.net

Jay
10/2/2003 3:12:40 PM

Hello,

I have an interesting design problem, I would like to have your opinion on.

I have table DRAWINGS, which has a field DrawingName which contains a list
of drawings with thier full path.
Eg- c:\winnt\mydrawing\room.dwg

I have a table ATTRIBUTES which has a field DrawingName which contains a
list of drawings without thier full path.
Eg- room.dwg

Now my objective is to delete all records in ATTRIBUTES table whose
ATTRIBUTES.DrawingName is not present in DRAWINGS.DrawingName.

When I initially implemented it I manually iterated the records to
accomplish it, Iam trying to find a better way to do it.

Thanks.
jay

Chris Taylor
10/3/2003 1:58:16 AM

Hi,

The following should do what I understand you require

delete from attributes
where not exists ( select 1
from Drawings
where right(DrawingName,len(attributes.DrawingName)+1 )
= '\'+attributes.DrawingName )

I included the '\' in the test to ensure that there are not false positives
where the end of one filename matches another filename.

Hope this helps

Chris Taylor

[quoted text, click to view]

asp.net : Interesting Database Query Question!!