fm and outer joins?

[Digitalbrit]

Hi - my db has a table of contacts (people), a table of organisations and a link table between the two (i.e. contacts at organisation).

The same contact can exist at many organisations (the link table resolves this many:many) however contacts can exist without being related to any organisation too.

I want to produce a query that will show a result set of contacts and their linked organisations (effectively 1 row for each link record), COMBINED with contacts that have no link records.

I could do this in Oracle using an outer join or combining two select results with a union - any advice on how to do this in fm would be really appreciated.

I have found an article which talks about effectively piping the two found sets into a temp table, but this seems heavy handed to me.

Many thanks

Tim

[comment]

See if this helps:

http://fmforums.com/forum/showtopic.php?tid/184230/post/239381/#239373

[Digitalbrit]

Thanks for the pointer "comment". I see this is suggesting using a portal, which I did play with but I wasn't getting the expected visual results (I need users to be able to then select a record from this found set).

I'll play with it again but if anyone can answer

a) are outer joins supported (as per SQL)

can result sets be unioned

Cheers

[comment]

a) no

no (or yes, by importing them into a common table)

However, I have never found this to be a real obstacle. As I said in the other thread, trying to apply SQL-thinking while working in Filemaker can be very frustrating.

I am not sure what exactly your purpose is here, but I wouldn't let users choose from a such a list, even if I could. As a user, I would find such task confusing: if I am supposed to choose a contact, then why do some of them appear more than once? Or am I choosing a company - but then why do I see contacts with no company? My preferred solution would be to split the selection process into two successive choices.

[Digitalbrit]

OK - thanks for the reply - at least I know what not to try.

As you say it depends on the purpose. Despite the fact the data needs be normalised out, the client prefers to have the results consolidated, as this is a more natural method of selection.

Cheers

Tim