SQL Comparison question

[Deleted member 11679]

Hello,

I have 2 tables with a column in each that I am looking to compare and filter out any that match. One table has been generated entered manually by the user, whereas the other table is a list of files from a directory that has been entered into Access through an ASP script.

Both columns contain the filename, however the filename in the column entered by the user is missing the file extension, this is held in another column in the table.

Anyway to compare the columns? The end target is to have a list of files that are currently held in a directory that have not been entered into the database, and from which can be.

If this sounds confusing I will try and explain more,

Any help would be great,

Tom

 

[rpstewart]

Should be do-able. So you want a list of files which have been added automatically but don't have a matching manual entry?

I'd start with

select filename from automatic_table 
where filename not in 
    (select filename||extenstion from manual_table)

Depending on how your manual add works you'll possibly have to concatenate a dot in as well as just concat'ing the filename and extension in the subquey.

 

[Deleted member 11679]

Precisely. However the automatically generated table does have file extensions which I don't want, so how would i perform a concatenation?

Testing that code now...

 

[rpstewart]

The double pipe I used in the subquery will do the concatenation, at least it will in DB2, no idea about any other DBMS.

 

[Deleted member 11679]

Cheers for your help, got it working To concatenate in Access it is... + ' ' +

Just had to do a bit of googling, so happy it works now!

 

[JIMA]

Hi,

Another way to do this would be to use a view which forms a set of the rows in the manual_table with the filename + extension concatenated. My concern would be that with the concatenation the primary key on the manual_table might not be used in the subselect so performance could be slow with a lot of data (depends on your DBMS).

I guess it depends on how much data you anticipate having and if your DBMS supports views as to whether this is worth doing.

Hope that's of some use.

Jim

 

[Deleted member 11679]

Hi,

Another way to do this would be to use a view which forms a set of the rows in the manual_table with the filename + extension concatenated. My concern would be that with the concatenation the primary key on the manual_table might not be used in the subselect so performance could be slow with a lot of data (depends on your DBMS).

I guess it depends on how much data you anticipate having and if your DBMS supports views as to whether this is worth doing.

Hope that's of some use.

Jim

Hello,

Cheers for the reply and solution. The data being held in the manual table will only ever reach a couple of hundred entries at most, so performance is not really an issue. It is being developed in Access sadly as well but you can't always have it your own way hey!