I have to tables, one which has a document_location field (parent - unique 

to a document) and a document_path field (children - connected with 

downloads of a document), the content of the two fields will match apart 

from that the document_path field is appended with E:/ for example:



table: documents

field: document_location 

KnowledgeBank/tools/overview.doc



table: downloads

field: document_path

E:/KnowledgeBank/tools/overview.doc



is there anyway to strip out the E:/ from the document_path content or add 

it to the document_location content so that i can match them up in a SQL 

statement.



please help me i am going mad

ps i never set up the system or i would have tied in a unique id

This message is in MIME format. Since your mail reader does not understand

this format, some or all of this message may not be legible.



------_=_NextPart_001_01C16D5E.1A482A58

Content-Type: text/plain



For SQL Server you could do the following



SELECT <fields>

FROM documents doc

    INNER JOIN downloads down ON

    RIGHT(down.document_path, LEN(down.document_path) - 3) 

doc.document_location



I'm not sure if RIGHT and LEN are supported by many other DMBSes. In fact to

be honest I am not entirely sure this work, but it should. Mind you this is

just a bandaid solution. The better short term solution is to script

something in VB, perl or whatever your chosen language is that adds "E:/" to

the start of every document_location field. The best solution is to add an

Identity. This one will bite you again unless you do.



regards

David Cameron

nOw.b2b

dcameron@i...



-----Original Message-----

From: dfalconer@g... [mailto:dfalconer@g...]

Sent: Wednesday, 14 November 2001 8:31 PM

To: ASP Databases

Subject: [asp_databases] problem with SQL comparing fields in different

tables





I have to tables, one which has a document_location field (parent - unique 

to a document) and a document_path field (children - connected with 

downloads of a document), the content of the two fields will match apart 

from that the document_path field is appended with E:/ for example:



table: documents

field: document_location 

KnowledgeBank/tools/overview.doc



table: downloads

field: document_path

E:/KnowledgeBank/tools/overview.doc



is there anyway to strip out the E:/ from the document_path content or add 

it to the document_location content so that i can match them up in a SQL 

statement.



please help me i am going mad

ps i never set up the system or i would have tied in a unique id







 




$subst('Email.Unsub')