A Join query

Hello everybody

I use Filemaker 9 pro.
I've a filemaker DB with some tables joined by relationships
I need to recover data in a way that in SQL is called "join query",
with a where clausole.

For example

Table Employee - Name, Surname, IdOrder
Table Order - idOrder, Item, Shipment, Country

SQL Statement
SELECT Name, Surname, Item, Shipment
FROM Employee INNER JOIN [Order] ON Employee.[IDOrder] = Order.
[IDOrder] WHERE Country="USA";

I need a Filemaker Format (report) in which I can see field Name,
Surname, Item, Shipment WHERE Country =USA.

How can I do?
Any suggestion?

Best regards

Re: A Join query

elisacarli wrote:

> Table Employee - Name, Surname, IdOrder
> Table Order - idOrder, Item, Shipment, Country
>
> SQL Statement
> SELECT Name, Surname, Item, Shipment
> FROM Employee INNER JOIN [Order] ON Employee.[IDOrder] = Order.
> [IDOrder] WHERE Country="USA";
>
> I need a Filemaker Format (report) in which I can see field Name,
> Surname, Item, Shipment WHERE Country =USA.

It's not clear to me why you would have and Order ID in the Employee
table, so I'm going to assume that what you meant is that you'd have an
Employee ID each table as your match field. That being the case...

Create a relationship from Order::IDEmployee to Employee::IDEmployee.

Perform a find from a layout based on the Order table, searching just
the Order::Country field for USA.

FileMaker has relationships instead of joins. Once you have established
a relationship, you can search on any field in either of the two tables.


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg
FM Professional Solutions, Inc. Los Angeles

FileMaker 8 Certified Developer
Member, FileMaker Business Alliance

Re: A Join query

On 17 Apr, 17:49, Howard Schlossberg
wrote:
> elisacarli wrote:
> > Table Employee - Name, Surname, IdOrder
> > Table Order - idOrder, Item, Shipment, Country
>
> > SQL Statement
> > SELECT Name, Surname, Item, Shipment
> > FROM Employee INNER JOIN [Order] ON Employee.[IDOrder] =3D Order.
> > [IDOrder] WHERE Country=3D"USA";
>
> > I need a Filemaker Format (report) in which I can see field Name,
> > Surname, Item, Shipment WHERE Country =3DUSA.
>
> It's not clear to me why you would have and Order ID in the Employee
> table, so I'm going to assume that what you meant is that you'd have an
> Employee ID each table as your match field. =A0That being the case...
>
> Create a relationship from Order::IDEmployee to Employee::IDEmployee.
>
> Perform a find from a layout based on the Order table, searching just
> the Order::Country field for USA.
>
> FileMaker has relationships instead of joins. =A0Once you have established=

> a relationship, you can search on any field in either of the two tables.
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Howard Schlossberg
> FM Professional Solutions, Inc. =A0 =A0Los Angeles
>
> FileMaker 8 Certified Developer
> Member, FileMaker Business Alliance

Thank you very much for your reply

That you described is just what I'm trying to do.
Could you give me more detailed informations on how to perfom a find
from a layout based on the Order table?
I'm new to filemaker and I would like some technical steps about this
operation.

Best regards

Re: A Join query

elisacarli wrote:

> On 17 Apr, 17:49, Howard Schlossberg
> wrote:
> > elisacarli wrote:
> > > Table Employee - Name, Surname, IdOrder
> > > Table Order - idOrder, Item, Shipment, Country
> >
> > > SQL Statement
> > > SELECT Name, Surname, Item, Shipment
> > > FROM Employee INNER JOIN [Order] ON Employee.[IDOrder] = Order.
> > > [IDOrder] WHERE Country="USA";
> >
> > > I need a Filemaker Format (report) in which I can see field Name,
> > > Surname, Item, Shipment WHERE Country =USA.


> That you described is just what I'm trying to do.
> Could you give me more detailed informations on how to perfom a find
> from a layout based on the Order table?


make a relationship as Howard says

make a report (listing style) of table Employee with fields Name,
Surname, item, Shipment, and the field country as not printable menu,
or select boxes, or other presentation as you like.

in the search mode, write USA in the contry field, and then return.

You can print the listing.

HTH