SQL join in JDBC?

[Michael J Horton]

I'm trying to do a simple inner join via JDBC, but all I get is an "invalid argument" error. I've even tried creating a very simple pair of files to test with (in case my real data was causing the problem):

Table 1: name.fp5

Name_ID Name

1 Fred

2 Joe

3 Sam

Table 2: phone.fp5

Name_ID Phone

1 555-1234

2 555-4321

1 555-1111

3 555-1357

I've tried 3 SELECTs - the first two work just fine, but not the third:

SELECT * FROM "name.fp5" WHERE "name.fp5".Name='Fred' (WORKS)

SELECT * FROM "phone.fp5" WHERE "phone.fp5".Name_ID=1 (WORKS)

SELECT * FROM "name.fp5", "phone.fp5" WHERE "name.fp5".Name_ID="phone.fp5".Name_ID

(ERROR)

The above syntax does work against MySQL and a MS SQL Server databases (which are my primary platforms) with the same setup. I've tried every variation that I can think of against the FM database with no success. Is there a way to get this to work?

My main limitation is that the final product will be doing a join on 9 tables (files), as this is the data the client provided.

[John Caballero]

Are the single digit numbers (the "1" that precedes "Fred" and the "1" that precedes "555-1234") each seperate fields? It's a little hard to tell by your example.

[Michael J Horton]

Heh - on my screen those were nicely spaced. Auto-formatters...

Yes, those are two seperate fields, with "name.fp5".Name_ID being a serial number, and "phone.fp5".Name_ID intended to be a reference to the first table.

[Michael J Horton]

I should also say, that what I expect from the final SELECT is a table like: (Though order may differ)

Name_ID Name Name_ID Phone

1 Fred 1 555-1234

1 Fred 1 555-1111

2 Joe 2 555-4321

3 Sam 3 555-1357

[omiossec]

Filemaker is not a real Relational db like Sql server or Oracle.

And the SQl syntaxe used with FMP jdbc is different as the standard sql language.

Join is not a part of the FMP/JDBC Sql

You need to use standard FMP relation and use an array to retreive data.