Searching records in two tables

[ifr4me]

04-17-09 04:44 AM - Post#34172

I have been using Foxpro and other SQL based databases for years and recently changed to a MAC and it seems that Filemaker doesn't support normal database constructs or concepts like joins, or SQL language queries. I have been trialing the software for the past 3 weeks and my question is that I am trying to figure out how to take one table (lets say it has 500 records with a field called "xtype" and I have another table that has 20,000 records also with a field called "xtype". I want to do a left outer join on the common field "xtype" where "xtype" in the smaller table matches "xtype" in the larger table the results are that all records from the smaller table are printed out and only those that match in larger table are printed out. If I was able to use SQL I would write a query like the following:

================================================

SELECT table1.xtype, table2.xtype, table2.field2, table3.field3, table2.field4;

FROM ;

table1 ;

LEFT OUTER JOIN table2 ;

ON table1.xtype = table2.xtype

=================================================

I have tried using the help section of Filemaker and that was not of any help. I called the pre-sales help line and they were of even less help other than to say ask the forum so here I am.

It seems that if I want to build a beautiful form and data entry system this application is great, however for raw data munging it seems to not follow any database standards I am familiar with.

Any help would appreciated.

thanks

[comment]

Filemaker is not a SQL database (although some SQL commands are supported when you query from outside via ODBC).

There is currently no support for a left outer join type of relationship. However, you can achieve your goal quite simply by defining a relationship matching on xtype, and printing all records from the small table, with fields from the large table placed on the same layout.