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

    Question Unanswered: Help with syntax please...

    Help with syntax...please!

    I have a table with duplicate filenames and directories. I need a way to select the filesets (all rows of one filename) that overlap in a certain directory. In the example data below I would need to select the 3 sets of files (file2, file3 and file4) because those sets include the "overlap" directory.

    SELECT [table].[File], [table].Path
    FROM [table]
    GROUP BY [table].[File], [K drive overlap].Path
    HAVING ((([table].Path) Like "K:\Dir-overlap*"));

    This only returns the part of the set...I want to group by the file and select the sets that contain at least one overlap field, but also retain the results from that set that aren't in the overlap directory. Any ideas?


    File....................Path

    File1..................K:\Dir1
    File1..................K:\Dir1
    File1..................K:\Dir3

    File2..................K:\Dir1
    File2..................K:\Dir-overlap
    File2..................K:\Dir3

    File3..................K:\Dir1
    File3..................K:\Dir-overlap
    File3..................K:\Dir1

    File4..................K:\Dir3
    File4..................K:\Dir-overlap
    File4..................K:\Dir3

    File5..................K:\Dir1
    File5..................K:\Dir3
    File5..................K:\Dir3
    Last edited by tburgan; 03-26-04 at 15:32.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Huh? I smell a FULL JOIN in there somewhere, but I'm still not sure even what you want.

    -PatP

  3. #3
    Join Date
    Mar 2004
    Posts
    3

    clarification...

    All the records are in one table, so from the example data above, I'd like to return these results with my query....

    (I realized that the formatting on the above post went wacky, so I fixed it here by adding some "........." to separate the columns)

    File............Path

    File2..........K:\Dir1
    File2..........K:\Dir-overlap
    File2..........K:\Dir3

    File3..........K:\Dir1
    File3..........K:\Dir-overlap
    File3..........K:\Dir1

    File4..........K:\Dir3
    File4..........K:\Dir-overlap
    File4..........K:\Dir3


    I need the complete set of records for each filename - but I only want the results from that set if it contains the the overlap directory...

    Does that clear it up?
    Last edited by tburgan; 03-26-04 at 15:30.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I think I understand what you want. Try using:
    PHP Code:
    SELECT *
       
    FROM [table] AS a
       WHERE EXISTS 
    (SELECT b.*
          
    FROM [table] AS b
          WHERE  b
    .[File] = a.[File]
             AND 
    b.[PathLIKE '%-overlap%'
    to see if that does what you want.

    -PatP

  5. #5
    Join Date
    Mar 2004
    Posts
    3
    Yeah! That worked great!

    I genuflect before your SQL greatness...

    Thanks for the help and the quick response!

Posting Permissions

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