Cant quite figure this one out... any help?

I have 2 tables, one contains the details for all the documents stored on a server such as the documents name and which directory it is stored in, and the other contains the details for each document version (if there are any) these are paired up using the documents id, per example below

Documents
ID, Name, Dir
1, word.doc, /docs
2, excel.xls, /docs
3, webpage.html, /docs

Document Versions
ID
1
1
1
2
2
3

so, as you can see, in the versions table there are 3 versions of 'word.doc' 2 of 'excel.xls' and 1 of 'webpage.html'. i need to write a query that will return all the documents in the versions table who have a matching document in the documents table (that is not the major problem though) the main problem is that i only want to return it if the matching record in the documents table passes certain criteria, this being that it is from a certain site and has a particular type of file name. for example, i would like to return only the document versions whose parent records are '.doc' or '.xls' files, but not html. this would leave me with :

Versions
ID
1
1
1
2
2

leaving the last record out, as it's parent is a html file.

As a bonus, if you could tell me how to append the parents name to each record so i get a table like this :

Results
ID Name
1 'word.doc'
1 'word.doc'
1 'word.doc'
2 'excel.xls'
2 'excel.xls'

that would be great, thank youYou didn't specify database engine you use; but, in Oracle it would be something like this:SELECT d.id, d.name
FROM DOCUMENTS d, VERSIONS v
WHERE v.id = d.id
AND SUBSTR(d.name, INSTR(d.name, '.', 1) + 1, LENGTH(d.name)) <> 'html';
WHERE clause will be different for another requirements, of course.|||hmmm, not sure if that's quite it. and the database engine is SQL server|||As I can't see a generic SQL solution, I'd recommend using:SELECT d.id, d.name
FROM documents AS d
JOIN versions AS v
ON (v.id = d.id)
WHERE d.name NOT LIKE '%.html'-PatP|||Got It! Thanks for your help though. This was the final solution for anyone who's interested

SELECT Docs.Name, DocVersions.TimeCreated, DocVersions.Content
FROM DocVersions INNER JOIN
Docs ON DocVersions.Id = Docs.Id
WHERE (Docs.LeafName LIKE '%.doc' OR
Docs.Name LIKE '%.xls' OR
Docs.Name LIKE '%.ppt' OR
Docs.Name LIKE '%.pdf' OR
Docs.Name LIKE '%.mpp' OR
Docs.Name LIKE ' % .txt ') AND (Docs.DirName LIKE 'sites/archive/%')

I think i confused u all with my talk of leaving out the html document. It wasn't so much i wanted to leave that out as include the others if you know what i mean

can't install sample databases plus no documents or tutorials

I have downloaded and run all the .msi's for the sample databases. To install them I am suppose to use "SQL Server 2005 Setup, you need to click the Advanced button....". I don't seem to have a "SQL Server 2005 Setup" - did the docs revert back to SQL Server rather than SQL Express or what ?

R
Perhaps I was unclear on the problem (people have looked at my post but no one has replied). I am trying to follow the instructions at

http://download.microsoft.com/download/d/8/6/d865cf0c-c44b-401b-b426-b3bf5c628112/GettingStartedWithSQLSamples.htm
Don't know why that doesn't show up as an URL.

Anyway the intructions do not match what I have downloaded.

Any help would be greatly appreciated

Thanks

R
|||

I downloaded the files couple of days ago on my own for a training. I think the process described here is for non-Express version, but for express version (or as a standalone inspection) you can download the sample files here:

http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks Jens for the reply and the link; however, that link is the crux of the problem. I have done everything specified on that link until I get to the point of using SQL Server 2005 Setup - which I don't have. I think I have a very neat application but it hard to implement it without a database to practice on.

R
|||

The first three steps of the Getting Started with SQL Server Samples document that your reference deal specifically with how to install the SqlSamples.msi file when you are installing SQL Server. These do not apply to SQL Express because we do not include the SqlSamples.msi as part of our installer package. (We do this to keep it small.) Steps starting with four still apply.

Rather, you need to install the samples separately by downloading SqlSamples.msi from the Samples page that Jens pointed to. You will see a bunch of links on that page that break the Sample installation files up by processor type, e.g. x86. The direct link for the x86 version is http://download.microsoft.com/download/a/1/8/a1872d6b-f740-4c52-a2c5-0eb5bdd1a7e3/SqlServerSamples.msi.

Once you've downloaded this file, just run it and it will install the Samples onto your computer. You can then use the Start menu (All Programs | Microsoft SQL Server 2005 | Documentation and Tutorials | Samples Directory) to open the directory and find the various sub-categories. Each specific sample has a read-me with more information about how to use it. For many, you will need AdventureWorksDB installed, which is also a separate download for Express and available on the same page with the samples. (i.e. the one that Jens gives.)