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

Mon Dec 9 20:20:26 MST 2013

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