5 Replies Latest reply on Jun 15, 2015 11:00 PM by fmdataweb

    ExecuteSQL - search on 2 tables and combined sorted results

    fmdataweb

      I've used ExecuteSQL in the past to do a simple search for records in a single table.

       

      I now have a situation where I would love to be able to use ExecuteSQL to search for records in 2 different tables and combine the results together and sort them - I'm not even sure if this is possible.

       

      I have 2 tables: MessagesIn and MessagesOut and I would like to do a search for records in both tables then combine the results and sort by the timestamp field so they are in order.

       

      Does anyone know if this is possible and what the syntax would look like?

        • 1. Re: ExecuteSQL - search on 2 tables and combined sorted results
          monkeybreadsoftware

          Sounds like UNION statement in SQL to join two SELECT queries.

          Not sure if FM would allow that and allow merging them.

           

          I could technically make two queries in my MBS Plugin and offer to merge both results.

          Even sort them. If you look for a plugin based solution, you could email me.

          • 2. Re: ExecuteSQL - search on 2 tables and combined sorted results
            beverly

            possibly using UNION to "append" results from more than one table. Keep in mind:

                  

                 the same number (and names) of columns must be in each SELECT. if they are named differently you may be able to use alias'

             

                 the ORDER BY (being the last clause) would appear after both SELECTs:

             

            SELECT A,B,C

                 FROM TA

             

            UNION ALL

             

            SELECT D as A, B, C

                 FROM TB

             

            ORDER BY B

                  

            On Jun 15, 2015, at 6:16 PM, fmdataweb <noreply@filemaker.com> wrote

             

             

            ExecuteSQL - search on 2 tables and combined sorted results

            created by fmdataweb in Discussions - View the full discussion

            I've used ExecuteSQL in the past to do a simple search for records in a single table.

             

            I now have a situation where I would love to be able to use ExecuteSQL to search for records in 2 different tables and combine the results together and sort them - I'm not even sure if this is possible.

             

            I have 2 tables: MessagesIn and MessagesOut and I would like to do a search for records in both tables then combine the results and sort by the timestamp field so they are in order.

             

            Does anyone know if this is possible and what the syntax would look like?

            Reply to this message by replying to this email, or go to the message on FileMaker Community

            Start a new discussion in Discussions by email or at FileMaker Community

            Following Discussions in these streams: Connections Stream

            Manage your email preferences

             

            FileMaker Developer Conference 2015 • Las Vegas, Nevada • July 20-23 • www.filemaker.com/devcon

             

            • 3. Re: ExecuteSQL - search on 2 tables and combined sorted results
              user19752

              Column names are not problem, you need same number of columns and data type matched on UNIONized SELECT list.

              • 4. Re: ExecuteSQL - search on 2 tables and combined sorted results
                beverly

                While that may or may not be correct, in some instances the NAME of column (especially if you may be sorting by it), DO need to be the same when using UNION.

                 

                Yes on the data type as well!

                beverly

                 

                 

                On Jun 15, 2015, at 9:36 PM, user19752 <noreply@filemaker.com> wrote

                 

                Column names are not problem, you need same number of columns and data type matched on UNIONized SELECT list.

                 

                • 5. Re: ExecuteSQL - search on 2 tables and combined sorted results
                  fmdataweb

                  Thanks to all who chimed in I was able to get this working without too much effort using UNION ALL.