10 Replies Latest reply on Nov 22, 2016 10:24 AM by user27070

    Help With SQL Query

    user27070

      Hello Everyone,

       

      Currently right now I have two tables, Models and Freight. Freight goes by branch and there should be 10 records per every model listed in here.

      I need to create a query where I can show model name related to freight values for each branch.
      for example:

       

      Model, Branch 1, branch 2, branch 3, branch 4, .......

       

      Model 1, 2000, 1000, Null or missing, 2000

       

      However there are a lot of missing values in the freight table, and im looking for an easy way to show my managers so they can go through and fix any issues. I am familiar with using Virtual Lists, however I cannot get a sql script to work. Am I supposed to use a INNER JOIN? If so, how?

       

      I do realize I can create a portal to the freight table and they can go through every model to find missing values. That is just a pain and they did not like that solution.

       

      Any help would be greatly appreciated. Thank you

        • 1. Re: Help With SQL Query
          beverly

          post your query so we may analyze.

           

          There is a SQL CASE() function that may help here (to return a "null"). Or other methods, but we need to know what you have that doesn't work.

          beverly

          • 2. Re: Help With SQL Query
            TSPigeon

            user27070:

             

            Thank you for your post!

             

            I am also going to move this thread from the FileMaker Community Feedback Space, which is specifically for input on the Community itself, to the Discussions Space where others might find your post easier and offer further advice.

             

            TSPigeon

            FileMaker, Inc.

            • 3. Re: Help With SQL Query
              user27070

              Hey there! Thank you for responding.

               

              So current i have the two tables linked on pk_model in the Model_2016 table and fk_model in the freight table.
              This is the SQL i have so far,

               

               

              ExecuteSQL ( "SELECT pk_model,

              (CASE Branch WHEN '101' THEN Freight_Price END),

              (CASE Branch WHEN '102' THEN Freight_Price END),

              (CASE Branch WHEN '103' THEN Freight_Price END),

              (CASE Branch WHEN '104' THEN Freight_Price END),

              (CASE Branch WHEN '105' THEN Freight_Price END),

              (CASE Branch WHEN '106' THEN Freight_Price END),

              (CASE Branch WHEN '107' THEN Freight_Price END),

              (CASE Branch WHEN '108' THEN Freight_Price END),

              (CASE Branch WHEN '109' THEN Freight_Price END),

              (CASE Branch WHEN '110' THEN Freight_Price END),

              From Model_2016" ; "|" ; "¶")

               

              The part I cant get my head wrapped around is the CASE function

              Branch and Freight_Price are from the freight table when the pk_model comes from the Model_2016 table.

              • 4. Re: Help With SQL Query
                beverly

                The context is irrelevant, however you can specify the table(s) and any JOIN needed. The relationship as defined on your graph is not used, so you must specify it in your query (as a JOIN).

                 

                Sent from miPhone

                • 5. Re: Help With SQL Query
                  user27070

                  Do you mean something like:

                   

                  ExecuteSQL ( "SELECT pk_model

                  FROM Model2016 LEFT OUTER JOIN Freight ON Model2016.pk_model = Freight.fk_model,

                  (CASE Branch WHEN '101' THEN Freight_Price END),

                  (CASE Branch WHEN '102' THEN Freight_Price END),

                  (CASE Branch WHEN '103' THEN Freight_Price END),

                  (CASE Branch WHEN '104' THEN Freight_Price END),

                  (CASE Branch WHEN '105' THEN Freight_Price END),

                  (CASE Branch WHEN '106' THEN Freight_Price END),

                  (CASE Branch WHEN '107' THEN Freight_Price END),

                  (CASE Branch WHEN '108' THEN Freight_Price END),

                  (CASE Branch WHEN '109' THEN Freight_Price END),

                  (CASE Branch WHEN '110' THEN Freight_Price END),

                  From Freight" ; "|" ; "¶")

                  • 6. Re: Help With SQL Query
                    beverly

                    so something like this:

                    a field in Model_2016

                    is set with:

                     

                     

                    ExecuteSQL ( "SELECT fk_model,

                    (CASE Branch WHEN '101' THEN Freight_Price END),

                    (CASE Branch WHEN '102' THEN Freight_Price END),

                    (CASE Branch WHEN '103' THEN Freight_Price END),

                    (CASE Branch WHEN '104' THEN Freight_Price END),

                    (CASE Branch WHEN '105' THEN Freight_Price END),

                    (CASE Branch WHEN '106' THEN Freight_Price END),

                    (CASE Branch WHEN '107' THEN Freight_Price END),

                    (CASE Branch WHEN '108' THEN Freight_Price END),

                    (CASE Branch WHEN '109' THEN Freight_Price END),

                    (CASE Branch WHEN '110' THEN Freight_Price END),

                    FROM Freight

                    WHERE fk_model = ?

                     

                    " ; "|" ; Char(13)

                    ; Model_2016::pk_model)

                     

                    pass the current PK (which would be the FK in the child table)

                    understand?

                    beverly

                    • 7. Re: Help With SQL Query
                      beverly

                      not quite. are you trying to pull a bunch of pk_models or (as my last reply with a query) just each "line" matching the pk_model in each record?

                       

                      get the pdf and example file(s):

                      beverly

                      • 8. Re: Help With SQL Query
                        user27070

                        trying to pull a report with roughly 100 different models,

                        ill take a the web page you just sent me and get back to you

                        • 9. Re: Help With SQL Query
                          erolst

                          You want to alert your managers so they can fix the issue - that issue being that that not each if the 10 related freight records has a value.

                           

                          So if you don't care about the actual values, and just want to know is the are less then 10 prices?

                           

                          Then this will give you that count:

                           

                          EcexuteSQL ( "

                            SELECT COUNT (*)

                            FROM Freight

                            WHERE

                              fk_model = ? AND

                              Freight_Price IS NOT NULL

                            " ; "" ; "" ; Model2016::pk_model

                          )

                           

                          Note that you don't neeed a join for such a simple query; just add the matchfield as another part of the WHERE clause.

                           

                          Also note that you could create a calc field with Count ( Freight::Freight_Price ), and you'd get the same result.

                          • 10. Re: Help With SQL Query
                            user27070

                            Hello Again,

                            I GOT IT WORKING! Finally! Thank you for your help Beverly.

                             

                            Erolst, i also got your example working. However they wanted to see the actual values to make sure the data is correct.

                             

                            THANK YOU AGAIN!