[FX.php List] About migrating to MySQL: Pulling data from multiple related tables

Tue Dec 10 13:11:35 MST 2013

Thanks for the help folks.

I'm using all the web resources, especially StackOverflow.  

I just needed to make a FileMaker reality check to confirm that I was on the right path.

It feels a little funny having a SELECT with 30 or so fields listed in a single line going offscreen.

Also, not quite sure when to use dbname.fieldname versus just fieldname versus dbname.fieldname as dbnamefieldname.

I'm learning...

Jonathan

On Dec 10, 2013, at 9:30 AM, Dale Bengston wrote:

> I also highly recommend Stack Overflow for coding help. I don’t think I would have ever gotten to a high level of jQuery implementation without Stack Overflow.
> 
> On Dec 10, 2013, at 10:58 AM, BEVERLY VOTH <beverlyvoth at gmail.com> wrote:
> 
>> Yes! Sitepoint is in my bookmarks and oft visited. Here's another (they also have a variety of things besides SQL) <http://www.w3schools.com/sql/default.asp>
>> 
>> LEFT JOIN (or even JOIN) == LEFT OUTER JOIN in most places. :)
>> I wanted to be specific, so that Jonathan understood the difference with OUTER & INNER. 
>> FYI: FMP doesn't support RIGHT OUTER JOIN  & FULL OUTER JOIN
>> 
>> I'm with Dale, be specific with columns/fields in SQL queries regardless of DB.
>> 
>> On 09 Dec 2013, at 10:20 PM, Dale Bengston wrote:
>> 
>>> I’m down to one FileMaker client at this point. All my other work is SQL. I love it, although it did take a while to get the hang of it. For the web, you really can’t beat it. MySQL is so much faster than FileMaker. So much.
>>> 
>>> Here’s a great (short) explanation of joins in MySQL:
>>> http://www.sitepoint.com/understanding-sql-joins-mysql-database/
>>> 
>>> One thing I like about SQL is it actually reads like a sentence:
>>> 	SELECT user.name, course.name
>>> 	FROM `user`
>>> 	LEFT JOIN `course` on user.course = course.id;
>>> 
>>> …you can read it out loud and it makes sense.
>>> 
>>> Since MySQL is not layout driven, be careful about what data you are extracting. I recommend being specific about the fields you’re pulling with each query. (In other words, no SELECT * FROM … )
>>> 
>>> Hope this helps,
>>> Dale
>>> 
>>> PS If I remember correctly, LEFT JOIN = LEFT OUTER JOIN in MySQL.
>>> 
>>> 
>>> On Dec 9, 2013, at 7:44 PM, BEVERLY VOTH <beverlyvoth at gmail.com> wrote:
>>> 
>>>> Not just an FMP developer... :)
>>>> I've been at this stuff since Netscape went public. (Long time). I also co-owed a web hosting company with MySQL & MS SQL servers. 
>>>> 
>>>> You probably can get away with just JOIN, but the LEFT OUTER JOIN ensures the parents with no children, too.  
>>>> 
>>>> Think of it a bit like the Relationship Graph. You have tables (TOs) and the links between them. Those are JOINs! 
>>>> 
>>>> HTH
>>>> 
>>>> -- sent from my iPhone4 --
>>>> Beverly Voth
>>>> --
>>>> 
>>>>> On Dec 9, 2013, at 5:09 PM, Jonathan Schwartz <jschwartz at exit445.com> wrote:
>>>>> 
>>>>> Thanks for the help, Beverly.
>>>>> 
>>>>> There is nothing like asking a group of FieMaker developers for getting started in the right direction. ;-)
>>>>> 
>>>>> Still a little foggy on the join type...If you search for a single record in Table A and that record contains a foreign key to pull a single value from a related Table B,  what type of  join would that be?
>>>>> 
>>>>> versus...
>>>>> 
>>>>> Same as above but wanting to get all related records from a related table (portal)?
>>>>> 
>>>>> Jonathan
>>>>> 
>>>>>> On Dec 9, 2013, at 1:19 PM, Beverly Voth wrote:
>>>>>> 
>>>>>> It's an OUTER join if you show all (FMP) records and their related children (portal). Some records may not have children, thus if you want records with and without children, it's LEFT OUTER JOIN (left meaning the parent perspective). 
>>>>>> 
>>>>>> If you export from FMP from parents and again from Children, you get different results, no?!
>>>>>> 
>>>>>> What are you doing with the results? Your perspective might be different, thus your query different. 
>>>>>> 
>>>>>> If you are wanting to pull more than one 'portal' it may or may not be separate queries. Again depending on what result you want. 
>>>>>> 
>>>>>> Perhaps you need to be asking this on a MySQL list, eh? :)
>>>>>> 
>>>>>> -- sent from my iPhone4 --
>>>>>> Beverly Voth
>>>>>> --
>>>>>> 
>>>>>>> On Dec 9, 2013, at 3:18 PM, Jonathan Schwartz <jschwartz at exit445.com> wrote:
>>>>>>> 
>>>>>>> Thanks Leo,
>>>>>>> 
>>>>>>> It's reassuring that I'm on the right track.
>>>>>>> 
>>>>>>> I've seen mention that the syntax on multiple join statements is more stringent than on single statements,  so maybe I need to use full syntax such as LEFT OUTER JOIN in stead of just JOIN.
>>>>>>> 
>>>>>>> Or...maybe my problem is that a FileMaker related query isn't a LEFT JOIN.  Is an INNER JOIN?
>>>>>>> 
>>>>>>> Jonathan
>>>>>>> 
>>>>>>> 
>>>>>>>> On Dec 9, 2013, at 12:13 PM, Leo R. Lundgren wrote:
>>>>>>>> 
>>>>>>>> You're on the right track. You simply join in multiple tables, can do that within the same query.
>>>>>>>> 
>>>>>>>> Here's a nice little visualization of various join types, thought it might be helpful in general:  http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html
>>>>>>>> 
>>>>>>>> 
>>>>>>>>> 9 dec 2013 kl. 21.09 skrev Jonathan Schwartz:
>>>>>>>>> 
>>>>>>>>> Hi Folks,
>>>>>>>>> 
>>>>>>>>> Haven't posted for awhile....
>>>>>>>>> 
>>>>>>>>> I'm doing my first migration to MySQL and need some advice on how to accomplish the same functionality on MySQl as on FileMaker.
>>>>>>>>> 
>>>>>>>>> Performing a simple query in a single table proved easy.
>>>>>>>>> 
>>>>>>>>> The problem is how to retrieve data from multiple related tables.  I added a "LEFT JOIN" statement to the MySQL query and successfully pulled data from one related table, but failed to get results from multiple related tables.  It could have been syntax errors on the subsequent JOIN statements.
>>>>>>>>> 
>>>>>>>>> Am I approaching this correctly?
>>>>>>>>> 
>>>>>>>>> In FileMaker, we often have many related tables and can pull from any (as long as they are on the layout).  I'm not getting the same vibe from MySQL as I explore the options.
>>>>>>>>> 
>>>>>>>>> I used brute force to get past the issue: I issued multiple queries, one to each table...but I'm pretty sure that's not the way to go. ;-)
>>>>>>>>> 
>>>>>>>>> Any guidance would be appreciated.
>>>>>>>>> 
>>>>>>>>> Thanks
>>>>>>>>> 
>>>>>>>>> Jonathan
>>>>>>>> 
>>>>>>>> _______________________________________________
>>>>>>>> FX.php_List mailing list
>>>>>>>> FX.php_List at mail.iviking.org
>>>>>>>> http://www.iviking.org/mailman/listinfo/fx.php_list
>>>>>>> 
>>>>>>> Jonathan Schwartz
>>>>>>> jschwartz at exit445.com
>>>>>>> 
>>>>>>> 
>>>>>>> 
>>>>>>> _______________________________________________
>>>>>>> FX.php_List mailing list
>>>>>>> FX.php_List at mail.iviking.org
>>>>>>> http://www.iviking.org/mailman/listinfo/fx.php_list
>>>>>> _______________________________________________
>>>>>> FX.php_List mailing list
>>>>>> FX.php_List at mail.iviking.org
>>>>>> http://www.iviking.org/mailman/listinfo/fx.php_list
>>>>> 
>>>>> Jonathan Schwartz
>>>>> jschwartz at exit445.com
>>>>> 
>>>>> 
>>>>> 
>>>>> _______________________________________________
>>>>> FX.php_List mailing list
>>>>> FX.php_List at mail.iviking.org
>>>>> http://www.iviking.org/mailman/listinfo/fx.php_list
>>>> _______________________________________________
>>>> FX.php_List mailing list
>>>> FX.php_List at mail.iviking.org
>>>> http://www.iviking.org/mailman/listinfo/fx.php_list
>>> 
>>> _______________________________________________
>>> FX.php_List mailing list
>>> FX.php_List at mail.iviking.org
>>> http://www.iviking.org/mailman/listinfo/fx.php_list
>> 
>> _______________________________________________
>> FX.php_List mailing list
>> FX.php_List at mail.iviking.org
>> http://www.iviking.org/mailman/listinfo/fx.php_list
> 
> _______________________________________________
> FX.php_List mailing list
> FX.php_List at mail.iviking.org
> http://www.iviking.org/mailman/listinfo/fx.php_list

Jonathan Schwartz
jschwartz at exit445.com