8 Replies Latest reply on Jun 18, 2009 6:17 AM by comment_1

    How To Present Data From SQL Union

    LeoCA

      Title

      How To Present Data From SQL Union

      Post

      We have similar data in various tables and need to compile a comprehensive list. The following SQL can generalize what we try to achieve: 

       

      Select field1, field2, field3 From Table1

      UNION

      Select field1, field2, field3 From Table2

      UNION

      Select field1, field2, field3 From Table3

      Order BY field1

       

      How can we get results by using FileMaker Pro 8.5?  Your directions are appreciated.

       

      LeoCA

        • 1. Re: How To Present Data From SQL Union
          comment_1
             Filemaker does support UNION. Perhaps you could import the three sets into a common table. There may be other options, but we'd need more details.
          • 2. Re: How To Present Data From SQL Union
            LeoCA
              

            Thank you for the response.

             

            We want to generate reports by pulling information from various tables that have similar information (that is why we use UNION in SQL).

            Do you think we need create tables for reports that require UNION information? There are many.

            Any other suggestions?

             

            Thanks.

             

            LeoCA

            • 3. Re: How To Present Data From SQL Union
              comment_1
                

              LeoCA wrote:
              We want to generate reports by pulling information from various tables that have similar information

              Yes, I understood that much, but I still have no idea what this information is and what's the overall purpose here. Generally speaking, similar information (i.e. described by the same fields) should be in the same table to begin with.


              • 4. Re: How To Present Data From SQL Union
                LeoCA
                  

                I agree with you that similar information should be in the same table.  However, I am generalizing my statement for communicating my question.

                The "similar information" I am referring to is the key fields existing in various tables of our application.  Each table have many items that are not similar among tables.

                 

                Thanks for your help.

                 

                LeoCA

                • 5. Re: How To Present Data From SQL Union
                  comment_1
                     I am afraid I cannot help you further with virtually nothing to go on.
                  • 6. Re: How To Present Data From SQL Union
                    FluffyBear
                      

                    Why don't you just set FM to join on the common key?  You said you got a key that's being shared among the tables. We do the same to join FM & SQL based tables.

                    • 7. Re: How To Present Data From SQL Union
                      LeoCA
                        

                      Example of problem:

                       

                      The organization has stored functions of travel, training, etc. in separate tables.  Try to determine certain staff's activities can be achieved by using SQL as:

                       

                      SELECT StaffID, field1 AS Activity_Date, field2 AS Description

                      FROM  Travel WHERE StaffID IN (...)

                      UNION

                      SELECT StaffID, field1 AS Activity_Date, field2 AS Description

                      FROM  Training WHERE StaffID IN (...)

                      UNION

                      SELECT StaffID, field1 AS Activity_Date, field2 AS Description

                      FROM  Special_Function WHERE StaffID IN (...)

                      ...

                      Order BY StaffID,Activity_Date

                       

                      I have exported those tables to Access and use SQL to fill requests.  I am trying to see if I can improve the process. 

                      I hope I make my question clearer and not confuse anyone.

                       

                      Thanks.

                       

                      LeoCA

                      • 8. Re: How To Present Data From SQL Union
                        comment_1
                           I still don't feel comfortable answering this, because:

                        Filemaker does not support UNION. There are various workarounds (I have already mentioned one, and I will mention another one below). These workarounds are just that - workarounds. They have their own advantages and limitations, they are not easy to explain and sometimes not easy to implement.

                        When you post an "example problem", you get an "example solution". Then inevitably comes the stage where you say "but my problem is actually a different one, and this won't work for me". That's why we ask posters to give us background information and some sense of their purpose. Without this, I would now have to write a treatise on the different ways to circumvent the fact that Filemaker does not support UNION, their comparative advantages and disadvantages, with examples of when it's better to use this one over another. Perhaps I will do so - but not here and not now.


                        Now, the example solution to your example problem is to define a supertype table of Activities and a number of subtype tables (Travel, Training, etc.), with a one-to-one relationship between Activities and each of the subtype tables. The fields common to all activities are in the Activities table, and this table can be used to produce a union report, or a portal of all activities on a person's layout.