I want create MySQL view that list families and rendersfor each family a list of all their members emails in text field.
What the SQL equivalent of List(members::email) ?
Great minds think alike!
This is what works:
GROUP_CONCAT(distinct people.b_personal_email SEPARATOR '
FROM families inner join people on people.aa_family_id=families.a_family_id
GROUP BY families.a_family_id
Puzzlingly I typed a literal CR in ' ' as the separator -- char(13) did not work
You should be able to so something like "Select distinct(email) from members where members.familyid=? "
Need more info. on your relationship to fill in the where clause.
Do you mean a MySQL calc using filemaker CWP? Or do you mean filemaker's ExecuteSQL function inside filemaker itself?
Assuming you have two tables, "Families" and "Members", related on Families::primaryIDKEY -<- Members::familyPrimaryIDKEY, and you are calculating a field called "Families::EmailList", the ExecuteSQL calculation would be:
WHERE familyPrimaryIDKEY = ?
; "" ; "" ; Familes::primaryIDKEY )
The MySQL for CWP is a bit more complex.
Here are the tables (They are ESS)
If I create a calc field in families : List(people::b_personal_mail) I could create layout bases on families that showed a_familiy_id and that calculated field.
I want to create a view in MySQL that effectively does the same thing.
Thats not what I want but thtanks. I want to create a VIEW in the Sql database itself.
So you are wanting to create a view in MySQL. What does this mean? Do you want to see a MySQL table in a FileMaker layout and do a search? Or are you using some type of SQL GUI program (e.g., Sequel Pro, Workbench, Navicat) to make an ODBC connection to FileMaker and issue a SQL call to view the results of a FileMaker table? If you are using a SQL GUI program (my favorite is Sequel Pro), then you have to make sure your ODBC connection is set up and working properly. Then everything works like normal SQL calls since FileMaker is SQL compliant.
After thought... I use Sequel Pro to connect to MySQL tables and I don't think it will connect directly to a FileMaker database. So you would have to use a different GUI tool.
Oh... now I think I see what you are wanting to do. You will have to use a MySQL tool (PHP MyAdmin or Sequel Pro or Navicat) to do that and you'll need a MySQL DBA person to tell you how to do that. Basically it creates a type of shadow table that is live and reflects data pulled from other tables. But FileMaker won't be able to help you with that.
My question has nothing to do with Filemaker in essence
I have a MySql database I manage with Navicat.
I want to create a view, or virtual SQl table and I need a SQl expression that will work like List does in FM.
So that answer to my question with be something like
sqlexpression AS 'emaillist'
and when I display my view in Navicat I will see something like
So you are wanting FileMaker to make a VIEW like it works in MySQL and that is a good question that I am note sure if it works or not. I take it you have already tried it and it didn't work. What happened when you did the "AS"?
No I do NOT want to do it Filemaker. I want to do it in the MySql database
This not a question about Filemker, it i s a question about MySQL
I apologize for my inability to express myself clearly.
But when I have it working I will reference the MySQL view as an ESS table.
you might find it more useful to post this in a place like stackoverflow.com or expertsexchange.com
A lot of the filemaker community is just now getting into MySQL syntax based on the introduction of ExecuteSQL with v.12
You should be able to directly connect Navicat to the MySQL database without FileMaker. The ESS tables are a unique FileMaker shadow table of a SQL database. It would be slower for you to connect to FileMaker and then have FileMaker's ESS talk to MySQL. Just have Navicat skip FileMaker and talk directly to MySQL. Is there any reason to not have Navicat talk directly to MySQL?
I DO have navicat talking to teh MysQL DB. I Lnoe hwo do set up Navicta and have used it for sevral years
Forget Filmker for now. My question is about teh SQL language and I posted here becasue there quite few SQl experts who subscribe.
With respect, Taylor if you do not know the SQL langauge, I don't think you can help.
Lee? Beverly? Help !!
Ah, OK, sorry I misunderstood.
So basically, you would like a return delimited list of values in a single column related to the families table?
When you do the query, do you want muliple instances of each family record to be returned (aka a flattened record structure)? In which case you can simply use a join. Or do you want the list of values in what is equivalent to a single field and one instance of each family record? In Oracle, I would write a function to go out and build a CR delimited list and use the returned value as a column in the view. I'm not up on MySQL, but I believe you can write inline functions as well. The select distinct() function will return a row for each distinct value.
I'll have to do a little research. I know MySQL has functions, but I'm not sure if they can be used inline in a query.
Yes thats it. I want one row per family.
Yes, you can use SQl functions in query
I want to do this inSQL, I am working over a SQL/FM/ESS applciation replacing complex relationships in Filemker with views in SQl. Over a WAN, SQL queries peformed on SQL host are MUCH faster than using relationships between ESS TOs in Filemaker. I have dramatically speeded up the application but building customer email lists is another area where I believe I can speed things up.
My apologies if we're having communication problems and I'm sorry for your frustrations. I think you are just asking how to make a join statement in SQL. A good web page that explains the JOIN function is at:
Writing custom functions (UDFs) in MySQL looks to be a bit of a pain. They have a function that looks like it might get you close called GROUP_CONCAT
Check this page for some examples. Sorry, I'm not real verse with MySQL.
Retrieving data ...