Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2004
    Posts
    3

    Unanswered: Can't 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 you

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You didn't specify database engine you use; but, in Oracle it would be something like this:
    Code:
    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.

  3. #3
    Join Date
    Nov 2004
    Posts
    3
    hmmm, not sure if that's quite it. and the database engine is SQL server

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As I can't see a generic SQL solution, I'd recommend using:
    Code:
    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

  5. #5
    Join Date
    Nov 2004
    Posts
    3
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •