1 2 Previous Next 20 Replies Latest reply on Feb 12, 2014 3:18 PM by synergy46

    SQLExplorer confusion...

    synergy46

      I am trying to get a leg up on ExecuteSQL so I am starting with SQLExplorer.

       

      I have two tables: Members and Drama

      They are (in FM) related by their key fields: Members::KP_MemberID and Drama::FK_MemID

      They are not connected in the TO in SeedCode SQLExplorer.

       

      No matter how I simplify the situation I get the dreaded ?

       

      What am I missing?

       

      SQL1.png

        • 1. Re: SQLExplorer confusion...
          BruceHerbach

          The issue maybe that there is no where clause.  So it doesn't know what to look for?  You could use a LIKE with a % to show all records.

           

          Bruce

          • 2. Re: SQLExplorer confusion...
            synergy46

            I guess I was confused by the (Optional) heading on SQLExplorer.  (In the search tab)

             

            So, I tried changing the code to accomodate 'where' and guessing I could set a global variable $$ID

            to the PK_MemberID and then use that variable to pull up all the Child records that match. 

             

            (648 is the PK_MemberID)

             

            I should think again.

             

            This is what I got...

             

            sql3.png

            • 3. Re: SQLExplorer confusion...
              BruceHerbach

              Hi,

               

              I took a look at my 12 version of SQLExplorer.  This is a slightly older version and it had very similar set up to the one you are using.   In a more current version they have replaced the example with one that does a Left Join.  You might try this approach.

               

              In this one they are looking for a name.  so the search string is "H%"  You could search on the ID field and use % to get all records.

               

              -> Old example

              SELECT a."CompanyName", b."ProjectName", a."CompanyUrl"

              FROM "Companies" a

              INNER JOIN "Projects" b ON a."CompanyID_kprime" = b."ProjectCompanyID_kf"

              WHERE a."CompanyName" LIKE ?

               

               

              -> New example

              SELECT a."CompanyName", b."ProjectName", a."CompanyUrl"

              FROM "Companies" a

              LEFT JOIN "Projects" b ON a."CompanyID_kprime" = b."ProjectCompanyID_kf"

              WHERE a."CompanyName" LIKE ?

               

              HTH

              Bruce

               

              One last thing you might look at Beverly Voth's SQLExplorer the missing manual.  I found it very helpful.  You can get it here:

              http://filemakerhacks.com/2012/10/19/the-missing-fm-12-executesql-reference/

               

              Message was edited by: Bruce Herbach

              • 4. Re: SQLExplorer confusion...
                synergy46

                I notice that even when I designate a parameter of 648 (numeric value) I get the error:

                648 is a verified PK_MemberID.  Changing the parameter to 648% or %648 also

                creates the error.

                 

                I am far from an SQL expert but shouldn't the select statement work?

                Also, I wrote a one line script that set $$IDMember to Member::PK_MemberID

                and after the I ran the script, $$IDMember shows as "" in the Data Viewer.  Huh?

                sql4.png

                • 5. Re: SQLExplorer confusion...
                  BruceHerbach

                  HI,

                   

                  In your text you use PK_MemberID however in the screen shot it is showing FM_MemID (b).  One last item is that in the screen shot it is showing KP_MemberID.

                   

                  As a next step,  I would go back to the parameter screen and change it to KP_MemberID (a) and then save and try the query.

                   

                  Regards

                  Bruce

                  • 6. Re: SQLExplorer confusion...
                    synergy46

                    What I am trying to do is:

                    List all the Drama::FK_MemID that hold the Member::PK_MemberID

                     

                    Soooooo, I use FK_MemID = 648 (PK_MemberID).  But even as a constant it does not work.

                     

                    I think I am missing something fundamental; I just can't see it.... grrrr

                     

                    Thanks for the reply

                     

                    Ron

                    • 7. Re: SQLExplorer confusion...
                      user19752

                      You write KP_ in query, not PK_ as Bruce mensioned.

                      Doesn't SQLExplorer check the field name?

                      • 8. Re: SQLExplorer confusion...
                        lkeyes

                        SELECT a."CompanyName", b."ProjectName", a."CompanyUrl"

                        FROM "Companies" a

                        LEFT JOIN "Projects" b ON a."CompanyID_kprime" = b."ProjectCompanyID_kf"

                        WHERE a."CompanyName" LIKE ?

                         

                        Hi @synergy46

                         

                        I've been querying mySQL back-ends through FM 12 and FM 13, so YMMV but: 

                         

                        1. I would simplify this and eliminate the parameters and the aliases for the moment. 

                         

                        SELECT CompanyName, ProjectName, CompanyUrl

                        FROM Companies

                        LEFT JOIN Projects ON Projects.ProjectComapnyID_kf =Companies.CompanyID_kprime

                        WHERE CompanyName = "Acme" 

                         

                        2. Note that you'll get the question mark as a result any time FileMaker SQL is confused or there is an error in the SQL syntax.

                         

                        3. Not sure why you put the field names in quotes in your original query, as most SQLs that I know don't use them there.

                         

                        4. When using the LIKE keyword...  the other side of the equal sign needs two % signs and are enclosed in quotes.   

                         

                        WHERE CompanyName LIKE "%ACME%"

                         

                        5. When you are using an ID are you matching field types...i.e. either text or numeric fields?

                         

                        Also, I would second Bruce's suggestion to check out Beverly Voth's paper, mentioned above.

                        • 9. Re: SQLExplorer confusion...
                          synergy46

                          I've isolated the problem to occur only when I specify a JOIN. 

                           

                          Can anyone see what's wrong with my join?

                           

                          As for the PK FK fields.... It seems to make sense to me.

                          I want to relate / connect the MembersPK with the DramaFK.

                           

                          What's wrong with this?

                           

                          sqlrelations.png

                          R

                          • 10. Re: SQLExplorer confusion...
                            wimdecorte

                            You don't need a join for your query since your result set does not include fields from two different tables.

                             

                            This should do:

                             

                            SELECT "Name_LastFirstMiddle"

                            FROM "Members"

                            WHERE "KP_MemberID" = ?

                             

                            and pass value of FK_MemID in as the parameter

                            • 11. Re: SQLExplorer confusion...
                              beverly

                              Is it an OUTER join (parents and any children)?

                              -- sent from my iPhone4 --

                              Beverly Voth

                              --

                              • 12. Re: SQLExplorer confusion...
                                synergy46

                                Hey Wim,

                                I limited fields to just one to see if I could get the thing to work.

                                Ultimately I need to list a single Member::KP_MemberID and all it's 'children' (FK_MemID)

                                 

                                The single field was used to try and get SQLExplorer to just 'work' and show something.

                                The 'one to many' configuration is as follows:

                                It too shows the dreaded '?'

                                 

                                sql5.png

                                • 13. Re: SQLExplorer confusion...
                                  user19752

                                  Why do you SELECT b."Degree"... but ORDER BY a."Degree" ?

                                   

                                  Returning "?" is nothing to do with.

                                  You would better to use sql.debug custom function in data viewer.

                                  • 14. Re: SQLExplorer confusion...
                                    wimdecorte

                                    That query does not make sense, since a.KP_memberID exists in b as FK_memID you could just do:

                                    (and I am making abstraction of the quotes around the field names)

                                     

                                    SELECT FK_memID, Degree, Role, Name

                                    FROM Drama

                                    ORDER BY FK_memID DESC, Degree ASC, Role, ASC

                                     

                                    All these fields exist in "Drama" according to your query so there is no need for a join

                                    1 2 Previous Next