1 2 Previous Next 21 Replies Latest reply on May 7, 2012 1:34 PM by philmodjunk

    (Left, Right) OUTER JOINS IN FM 12?

      Title

      (Left, Right) OUTER JOINS IN FM 12?

      Post

      Is there any way in FM 12 to do Left Outer Joins, for example?


      Say I have a customer table joined to an orders table. I want to list all customers whether or not they have associated orders.

       

      The FM join way as I've read about it is only an Inner join. Therefore, in the case I described above, I'd miss all the customers with no orders!

       

      There has to be a good way around this, right?

       

      Since this is now version ... 12 of the product, I'm guessing there's a good way to do something as basic as Outer Joins...

       

      Is this a case where I'd have to write a script or does FM support Outer Joins like other databases do now?

       

      Thanks in advance.

       

      - m

        • 1. Re: (Left, Right) OUTER JOINS IN FM 12?
          Jason Wood

          If you want to list all customers wouldn't you go to customers and show all records?

          Are you trying to show all customers in a popup menu or portal in Orders? Then you could use a relationship with the x operator "all rows, or cartesian product".

          • 2. Re: (Left, Right) OUTER JOINS IN FM 12?

            I am trying to be able to show all customers wheter or not they have associated orders.

            Say you have the following customer table

            CUS_PK   CUS_NAME

            1                Phil

            2                Steve

             

            And, the following orders table

            ORD_PK   CUS_FK   ORDER_ITEM

            1               1             sweater

            2               1             jeans

             

            If you did a regular select statement (an inner join, the kind FM does as I understand it), you would never see Steve since he doesn't have an order in the orders table.

            In regular (yes, still simple) SQL, you just create a LEFT OUTER JOIN, like this:

            SELECT CUS_NAME, ORDER_ITEM from CUSTOMER

            LEFT OUTER JOIN ORDERS

               ON CUS_PK=CUS_FK

            WHERE .....

            Now, you would see Steve even though he deosn't have any orders. The ORDER_ITEM in the result set for Steve would just be null.

            ---------

            In DB Terms, a Cartesian Product sounds like a scary thing since it's a TableA * TableB size result set. So, if table A had 100,000 rows, and table B had 100,000 rows, the cartesian product would have .... 10,000,000 rows!!!! Yikes!

            OUTER JOINS have been around almost as long as electricity. :)

            There must be some type of support in FileMaker for these necessary queries.

            The apparent lack of Outer Join support is the main reason I have yet to buy FM.

            It must be the case that FM does not actually use SQL for its internal database so things like Outer Joins are beyond its architecture?

            Having said that, I have no way of knowing what FM does internally for sure....I just need to be able to solve the problems I can solve using the Free MySQL, Oracle XE, or even, gulp, Access.

            Hints, suggestions....???

            Thanks...m

            • 3. Re: (Left, Right) OUTER JOINS IN FM 12?
              GuyStevens

              I think what you are trying to do is show a list view all customers.

              This is a situation where you don't even use relationships or joins.

              Maybe it works differently in SQL but in Filemaker this is quite straightforward.

              You just make a layout based on your client table and list all the records.

              • 4. Re: (Left, Right) OUTER JOINS IN FM 12?
                Sorbsbuster

                I've never come across this limitation in 20 years of using Filemaker, so I assume I have mis-understood your question.  When you said "I want to list all customers whether or not they have associated orders" I thought like jasonwood that you would simply find all customers in the customer table.  Why would you want to see all customers, but start the search from the order items table?  Maybe if I understood the basis of that requirement I could help.

                A Cartesian relationship between two tables each of 10,000 records will not result in a returned set of 100,000,000.  From each table the relationship will display the opposite table's 10,000 records.

                If you have 3 customers each of whom has bought items:

                Customer    Order items
                1                      A
                                        B
                                        C

                2                      A
                                        B

                3                      A
                                       C

                Searching in the Customer table for item A will return 3 records (1, 2, 3)
                Searching in the Customer table for item C will return 2 records (1, 3).
                Searching in the Customer table for NOT item B will return 1 record (3)

                Searching in the Order Items table for Item A will return 3 records (from customers 1, 2, 3).
                Searching in the Order Items table for Item C will return 2 records (from customers 1, 3).
                Searching in the Order Items table for NOT Item B will return 5 records (the order items A, C, A, A, C).

                Searching the the Order Items table for Customer 1 will return 3 records (A, B, C)
                Searching the the Order Items table for Customer 3 will return 2 records (A, C)
                Searching the the Order Items table for NOT Customer 3 will return 5 records (A, B, C, A, B)

                Searching the customers table for all customers who have not ordered item B would return 1 record (3)

                If you want a total order count by customer, then showing all customers in the customer table with a calculation field of ItemCount from the Items Table will show that, even if they have ordered nothing.

                If you want a total order count by customer for a particular item, then searching in the Customer Table for Item A, then showing the found set of customers in the customer table with a calculation field of ItemCount from the Items Table will show that.

                I am struggling to think of what it is that you are trying to achieve that isn't covered by those permutations.

                • 5. Re: (Left, Right) OUTER JOINS IN FM 12?

                  From your example above, each of your customers has at least one item in the order items table. That's not the situation I was describing.

                  Speicfically, if Customer 2 did NOT have any orders Order items table, an inner join would not show that customer at all.

                  By Inner Join, I mean the following query:

                  select customer_name, order_item where customer.cus_pk = orders.cus_fk where ....

                  This query would only show those customers and order items where there was both a customer and an order for that customer, but NOT show any customers with no orders.

                  A left outer join fixes this issue by showing all customers and orders whether or not the customer has any orders. Of course, for those customers with no orders would have nulls for their order item field.

                  So to reviese your listing above, in my case, it would look more like this:

                  Customer    Order items
                  1                      A
                                          B
                                          C

                  2                     (no order items)
                                        

                  3                      A
                                          C

                  Here, Customer 2 has no orders. Yet, in the listing of customers and order items, I still want to see that customer.

                  Can you verify that if I have the customer table at the top and then put a portal below it for Orders, I would see customers 1, 2, and 3 and see the orders for customers 1, and 2?

                  That's the basis of my question.

                  I appreciate your reply and please realize it may very well be *me* who's confused about FM [the reason for my post]. :)

                  - m

                   

                   

                  • 6. Re: (Left, Right) OUTER JOINS IN FM 12?
                    Sorbsbuster

                    "Can you verify that if I have the customer table at the top and then put a portal below it for Orders, I would see customers 1, 2, and 3 and see the orders for customers 1, and 2?"

                    If you have a relationship by Customer ID from the Customer Table to the Order Items table, and on the Customer Layout you place a portal showing the records from the Order Items table, then on:

                    - customer record 1 the portal will display 3 rows
                    - customer record 2 will display an empty portal
                    - customer record 3 will display 2 rows.

                    -------------------------------------------------------------

                    "This query would only show those customers and order items where there was both a customer and an order for that customer, but NOT show any customers with no orders."

                    From the customer table, show all customers.  A calculation, for example, would display how many order items they had, including zero, so you would be viewing all customers and their order item count, including those customers who ordered nothing.

                    ------------------------------------------------------------

                    Maybe my confusion in your requirement is from this, for example: "Yet, in the listing of customers and order items..." - do you mean a listing derived from the customer table, of from the order items table?

                    Filemaker will happily start a 'Find' in one table and return the records in that table that meet the search criteria (including by relation), and will equally happily search in one table, then show all the records in another table that are related (or NOT related) to that original found set.  But a returned set of records has to belong to a table.

                    • 7. Re: (Left, Right) OUTER JOINS IN FM 12?

                      - customer record 1 the portal will display 3 rows
                      - customer record 2 will display an empty portal
                      - customer record 3 will display 2 rows.

                      Yes, that sounds like a LEFT OUTER JOIN as long as I still see customer record 2's information in the layout.

                      Would the relationship be a simple join between the two tables?

                      -----------------------

                      I really like FM, but wish it had a SQL interface so I could do manual ad-hoc queries in a window for data analysis (without using a form and fields). Is there a way to do that (for the internal FM database)?

                      -m

                      • 8. Re: (Left, Right) OUTER JOINS IN FM 12?
                        Sorbsbuster

                        Create a relationship between the Customer Table and the OrderItem table, related by CustomerID.  In the customer table create a calculation field:

                        Count ( CustoerTableToOrderItem::OrderItemID )

                        Display that on a List View of the customers in the Customer Table.  Show all customers.  You will see all customers listed with their count of order items including zeros.

                         

                        ----------------------------------------

                        "wish it had a SQL interface so I could do manual ad-hoc queries in a window for data analysis (without using a form and fields)"  Of all the things in all the world that users like about Filemaker, it has to be the almost-intuitive way that very precise searches can be built up.  I think you are simply experiencing the upsides of familiartity with SQL and lesser familiarlty with Filemaker, and trying to make Filemaker's search ethos fit your experience.  For those with SQL training it might seem easy, but for common-or-garden users QBE is much, much easier to understand.

                        Which aspect of entering 'Find' mode and entering the data you want to search for is throwing you?  What data do you want to search for that is not already on a layout?  If it is visible on a layout it is searchable (bascially) and even when it is not visible it can be searched by a script.

                        • 9. Re: (Left, Right) OUTER JOINS IN FM 12?

                          Good answer thanks.

                          The Find interface is good. I was just thinking it would also be nice to just have a window for "power find" where you could enter queries directly.

                          Thanks,

                          -- m

                          • 10. Re: (Left, Right) OUTER JOINS IN FM 12?
                            Dillik

                            "I really like FM, but wish it had a SQL interface so I could do manual ad-hoc queries in a window for data analysis (without using a form and fields). Is there a way to do that (for the internal FM database)?"

                            Well, there's the ExecuteSQL function that was introduced in 12 (and mysteriously under-promoted, as far as I'm concerned).  You could, for example, use ExecuteSQL in the data viewer to see results independent of layout context and relationships.  Unfortunately, the only type of joins that are currently supported are inner joins, but I'm still a big fan of the function's existence.

                            • 11. Re: (Left, Right) OUTER JOINS IN FM 12?

                              I was actually hoping for a SQL interface for the internal FM database.

                              Maybe that's what you meant?

                              Are you saying that queries I send to the remote database are limited to inner joins?

                              I must be misunderstanding this...

                              If that's true, I'll never buy FM...

                              - m

                              • 12. Re: (Left, Right) OUTER JOINS IN FM 12?

                                Well, I hope you could at least have aliases or someway around the (badly missing) Outer Join Support, right?

                                I want to like (and buy FM), I really do.

                                As a database developer, I'm concerned that things that are falling off the log easy in, say, Java, are next to impossible or quite difficult in FM.

                                I've never, for example, had to concern myself if Outer Joins were supported or having to come up with some clever way around them.

                                I guess I'm still in "wait mode" for a while ...

                                Maybe when the FM 12 book comes out in June I'll get that and read up.

                                Or maybe wait 'til 13, 14, .... :(

                                -m

                                • 13. Re: (Left, Right) OUTER JOINS IN FM 12?
                                  Sorbsbuster

                                  I'm still missing what purpose of an Outer Join you think is missing from FM.

                                  • 14. Re: (Left, Right) OUTER JOINS IN FM 12?

                                    I was responding to another person's post where he said that only inner joins were available for the new ExecuteSQL command.

                                    Sorry my posting above wasn't clear.

                                    From a database perspective inside FM, it seems like outer joins are handled OK.

                                    Have you used the new ExecuteSQL command introduced in FM 12?

                                    I would like to hear your comments. :)

                                    Thanks.

                                    -m

                                    1 2 Previous Next