11 Replies Latest reply on May 1, 2012 8:24 AM by Rodney Michels

    T-SQL

    Rodney Michels
    Rodney Michels Apr 26, 2012 5:35 PM

      Team,

       

      Can someone tell me if it is possible to find all the fasteners in out vault by writing a select statement using T-SQL?  I want to make sure users are not copying these files out of the designated location and putting them in other folders.  Maybe by the File Type of something?

       

      T-SQL.png

      • 1296 Views
      • Categories: Data Management, Enterprise PDM
      • Tags:
        • Re: T-SQL
          Lee CS Young
          Lee CS Young Apr 27, 2012 9:08 AM (in response to Rodney Michels) 
          SELECT D.Filename
    ,P.Path
FROM [Documents] D
JOIN DocumentsInProjects DIP ON DIP.DocumentID = D.DocumentID
JOIN Projects P ON P.ProjectID = DIP.ProjectID
WHERE D.DocTypeID = 3

           

          DocTypeID = 3 is a toolbox part.

           

          You could filter it a little more by adding something like -> AND P.Path NOT LIKE '\Toolbox%'.

            • Re: T-SQL
              Rodney Michels
              Rodney Michels Apr 27, 2012 4:31 PM (in response to Lee CS Young)

              Thanks Lee for the response.  Although I think this is pretty close, not exactly what I was looking for.  Maybe this is not do-able.  I think if I am understanding this correctly, the DocTypeID is what EPDM call the Catagory, correct?  If so, what I am trying to do, that wont work because a toolbox part can have the same catagory as any solidworks file.

               

              Sorry, I am sure I am confusing you now.

               

              FileType.png

                • Re: T-SQL
                  Lee CS Young
                  Lee CS Young Apr 30, 2012 9:40 AM (in response to Rodney Michels)

                  Ah, yes, sorry about that. The DocType does map to categories.

                   

                  Try this:

                  SELECT D.Filename
    ,P.Path
FROM [Documents] D
JOIN DocumentsInProjects DIP ON DIP.DocumentID = D.DocumentID
JOIN Projects P ON P.ProjectID = DIP.ProjectID
WHERE D.Flags = 2 OR D.Flags = 4
                    • Re: T-SQL
                      Rodney Michels
                      Rodney Michels Apr 30, 2012 10:09 AM (in response to Lee CS Young)

                      Lee,

                       

                      THIS IS IT!!!!!  Thanks so much for your help!

                      • Re: T-SQL
                        Rodney Michels
                        Rodney Michels Apr 30, 2012 4:24 PM (in response to Lee CS Young)

                        Lee,

                         

                        Can you help me understand what exactly these two lines are doing?

                         

                        JOIN DocumentsInProjects DIP ON DIP.DocumentID = D.DocumentID

                        JOIN Projects P ON P.ProjectID = DIP.ProjectID

                         

                        I understand you are Joining the two tables, declaring DIP as DocumentsInProjects, but why are you having to join DIP.DocumentID and make it equal to Document.DocumentID?

                         

                        Secondly, again, I understand the second Join, I just dont know why you have to make Project.ProjectID equal to DIP.ProjectID?

                         

                        I hope I made sence there.

                         

                        Again, this is what I wanted, but I just want to understand so I can start doing this stuff myself.

                         

                        Thanks Again Man!

                         

                        Rodney

                          • Re: T-SQL
                            Brian Dalton
                            Brian Dalton Apr 30, 2012 4:36 PM (in response to Rodney Michels)

                            It has to do with the structure of the database.  The Documents table lists the documents and the Projects table lists the projects (EPDMese for folders), but the interconnecting table is DocumentsInProjects, which provides the links between each document and its project.

                             

                            The query asks for a field from the Documents folder (D.Filename) and a field from the Projects folder (P.Path) for all records in which D.Flags = 2 or 4.  In order to look up the Path field from the Projects table which corresponds to the selected document in Documents, joins are used to follow the connection from Documents through DocumentsInProjects to Projects.

                             

                            The link between Documents and DocumentsInProjects is the shared key field 'DocumentID'

                             

                            The link between DocumentsInProjects and Projects is the shared key field 'ProjectID'

                             

                            So in order for a document to 'see' a value relating to the project (folder) that it is associated with, it must follow the shared key field links through the interconnecting table.

                             

                            Clear as mud?