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

    T-SQL

    Rodney Michels

      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

        • Re: T-SQL
          Lee CS Young
          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

              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

                  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

                      Lee,

                       

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

                      • Re: T-SQL
                        Rodney Michels

                        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

                            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?