Interesting Database Query Question!!

[Jay]

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\mydraw ing\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.Draw ingName is not present in DRAWINGS.Drawin gName.


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]

Re: Interesting Database Query Question!!

Hi,

The following should do what I understand you require

delete from attributes
where not exists ( select 1
from Drawings
where right(DrawingNa me,len(attribut es.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

"Jay" <programmer@atg inc.com> wrote in message
news:ecb0IGSiDH A.1964@TK2MSFTN GP10.phx.gbl...[color=blue]
> Hello,
>
> I have an interesting design problem, I would like to have your opinion[/color]
on.[color=blue]
>
> I have table DRAWINGS, which has a field DrawingName which contains a list
> of drawings with thier full path.
> Eg- c:\winnt\mydraw ing\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.Draw ingName is not present in DRAWINGS.Drawin gName.
>
>
> 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
>
>
>
>
>
>
>
>[/color]