9 Replies Latest reply on Dec 10, 2009 8:50 AM by philmodjunk

    Where are INNER JOIN / OUTER JOIN

    aria

      Title

      Where are INNER JOIN / OUTER JOIN

      Post

      Hi All,
      Using Filemaker Pro 10.x.
      In File\Manage\Database...\Relationship. When I create a relationship between multiple tables, I could not find any INNER JOIN / OUTER JOIN functions. Could you please let me know how t accomplish this? Or if Filemaker does not have them, then how do we go around that?
      Thanks,
      A


        • 1. Re: Where are INNER JOIN / OUTER JOIN
          philmodjunk
             In Filemaker you get the same results simply by linking the Table Occurrence boxes using the operators provided. Filemaker can "tunnel" from one TO through an intermediate TO to a third TO, Provided such a combination of relationships is valid.
          • 2. Re: Where are INNER JOIN / OUTER JOIN
            mrvodka
              

            There are no true outter and inner joins. FileMaker is very keen on perspective.

             

            For example, if you do a show of the parents records and have a portal showing the child records, that is kind of left join. It will show you all the parent records and the matching related child records.

             

            If you do a find on the parent layout but in the child field, the resulting set becomes like an inner join as it will only display parent records with your find criteria and display the child records.

             

            Does that kind of make sense?

            • 3. Re: Where are INNER JOIN / OUTER JOIN
              aria
                

              Hi mr_vodka,

              I tried your suggestion, but the report output result is not what we are getting in MS Access Query Design.
              Our core users are MS Access savvy and they expect to see the report output result as MS Access does.
              I have a FMPRO test file and Ms Access database both database contains several tables that contain several simple test records.
              In MS access I already created a Query1 that shows what we expect from FileMaker to do.
              I do not see any add attachment button in this forum. I wish there was a way to post my FMPRO and MS access test tables as an attachment so we could compare them side by side for the report output that I’m expecting from Filemaker pro.


              Thanks, for any help.


              A


              • 4. Re: Where are INNER JOIN / OUTER JOIN
                mrvodka
                  

                I put my files here.

                 

                http://www.4shared.com

                • 5. Re: Where are INNER JOIN / OUTER JOIN
                  aria
                    

                  Hi mr_vodka,
                  Here are the links:
                  http://www.4shared.com/file/167496742/fc914e4c/FMPRO_ACCESS1.html

                  For FMPRO test db there is a username: admin and there is no password
                  http://www.4shared.com/file/167501521/6bdde573/TO_FM_SUPPORT2.html


                  Thanks in advance,


                  A




                  • 6. Re: Where are INNER JOIN / OUTER JOIN
                    mrvodka
                      

                    The report wont look like what you would expect in Access. In your Access exmaple. you are using multiple inner joins with a criteria of 'X'.

                    Well in FileMaker, you could have a layout with multiple portals to each table. So for WASI, II, and III there would be separate portals when you search for a record in the ID table for 'X'. However, there is no way to combine all 3 tables in one result.

                     

                    A better question is what are you trying to do and is is 100% necessary to have it the way it currently is in Access.

                     

                    • 7. Re: Where are INNER JOIN / OUTER JOIN
                      aria
                        

                       

                      So far your suggestion using multipleportals works great, and the rest shouldn’t be any issue for our users.

                      Thanks for all the help,

                      A

                       

                      • 8. Re: Where are INNER JOIN / OUTER JOIN
                        davidanders
                          

                        Following the thread, is there a major fault in FMP that is implicent in Access?

                        Or is there a guilded path that leads to a false result in access?

                        A simplier path with with Filemaker that supercedes another path in Access? 

                        • 9. Re: Where are INNER JOIN / OUTER JOIN
                          philmodjunk
                            

                          Since davidanders asked...

                           

                          I've worked with both DB systems.

                           

                          Access "Pros"

                          1. Usually already installed on a windows machine--often no need to spend additional $$ to add a new database.
                          2. Full up text editor for Scripts fully integrated with a debugger and value "watch" system in the standard release
                          3. All Queries are SQL which permits additional actions like making a new table, Unions and cross tab queries all possible via SQL expressions
                          4. Drop down lists are much more powerful, flexible. (You can have a 5 column drop down list sorted on a field not visible in the drop down.)
                          5. Scripting is done in Visual Basic for Applications--a much more powerful object oriented programming tool with many more options for interacting with the database, the OS and other applications.

                          Access "Cons"

                          1. Won't run on Mac OS.
                          2. Horrible record locking (Access locks a group of adjacent records instead of just the one a user opened for editing.)
                          3. Inadequate account/password security (You can often hack your way in just by deleting the password file.)
                          4. All Queries are SQL which means simple queries like "Show me everyone who works in California" are much more cumbersome than FMP.
                          5. Design philosophy is more "fine grained". Sort of like building a house with individual nails and boards where Filemaker is more like snapping together pre-fab modules. This can lead to much longer development process and much higher Development costs.
                          6. All "calculation field" type objects are either unstored calcs entered into a layout object or defined in a SQL query expression--tends to make such objects "layout specific".
                          7. Changing the group of records displayed on a form requires writing code to manipulate the form's record source (usually by constructing and executing a new SQL expression.) as opposed to simply performing a find in Filemaker.
                          8. Can't "Scale" up to support a large user base without migrating the system to a completely different database app such as SQL Server.