join from a ?combined? columns

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

SQL Server 2005 Forums

Transact-SQL (2005)

join from a ?combined? columns

Author

Topic

pithhelmet
Posting Yak Master

183 Posts

Posted - 2007-09-06 : 09:40:15

Hi everyone -

I have two tables, each table needs
columns to be combined to create a path (location on hard drive)

first table combines into a field called file1
second table combines into a field called file2

SELECT DatabaseImagesList.Path + DatabaseImagesList.Directory + '\' + DatabaseImagesList.FileName AS file1

SELECT DiskImagesList.Folder + DiskImagesList.DiskFilename AS file2

I need to do a join between databaseimageslist and diskimagelist
tables when the file1 = file2 (i will need to make them lowercase in the process) and get a list of all files that are NOT
in the diskimagelist table (meaning the files are listing in the database, but they are not actually on the hard drive)

thank you for your help

take care
tony

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-06 : 10:01:14

Start with

Select t1.columns, t2.columns from
(
SELECT DatabaseImagesList.Path + DatabaseImagesList.Directory + '\' + DatabaseImagesList.FileName AS file1
) as t1
inner join
(
SELECT DiskImagesList.Folder + DiskImagesList.DiskFilename AS file2
) t2
on t1.file1=t2.file2

Madhivanan

Failing to plan is Planning to fail

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-06 : 14:34:07

select Folder + DiskFilename
from DiskImagesList
except
select path + directory
from DatabaseImagesList

or

select folder + diskfilename
from diskImagesList t
where not exists (select * from DatabaseImageList
where (path+ directory)=t.folder+ t.diskfilename )

or

select folder + diskfilename as File1
from diskimagelist t1
left outer join
(select path + directory as File 2 from databaseimagelist) t2 on t1.folder+t1.diskfilename=t2.file2
where t2.file2 is null

--------------------
keeping it simple...

pithhelmet
Posting Yak Master

183 Posts

Posted - 2007-09-06 : 15:32:18

Thank everyone for the help

These solutions work except for the point that i need to
see the records that are not in the diskimageslist table.

I need to know what images are in the database (databaseimageslist) that
do not have a matching file in the diskimagelist

thank you again for your help

take care
tony

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-06 : 15:35:23

reverse the placing of the tables in the queries provided?

or i'm too simple minded to understand what you really require

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources