[Solved] LEFT JOIN with 3 tables

[humulus]

Hi, thank heaven for this forum. I've just learned how to fix when Base can't connect to database.

A new theme
I have made a query using JOIN with two tables which works. Now I've tried to modify it to use 3 tables (added TA_Pagina) and I have looked around to find information about it, trial and error, trial and error but I can simply not get them to work. This is one of them, perhaps not the best one but I can't tell which is the best, I've tried a lot of variants. I get this error message. "Syntax errror, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE".

SELECT
"TA_Församling"."Församlingsnamn",
"TA_Volym"."Volymnummer",
"TA_Volym"."ID_Volym",
"TA_Pagina"."Paginanummer"
FROM { OJ "TA_Församling" LEFT OUTER JOIN "TA_Volym", " TA_Pagina"
ON "TA_Församling"."ID_Församling" = "TA_Volym"."FK_Församling"
AND WHERE "TA_Volym"."ID_Volym" = "TA_Pagina"."FK_Pagina" }

ID* and FK* are INTEGER as PRIMARY and FOREIGN KEYS.
Lars

[FJCC]

I would use a syntax like this:

Code: Select all

SELECT 
"TA_Församling"."Församlingsnamn", 
"TA_Volym"."Volymnummer", 
"TA_Volym"."ID_Volym",
"TA_Pagina"."Paginanummer"
FROM "TA_Församling" LEFT OUTER JOIN "TA_Volym" 
ON "TA_Församling"."ID_Församling" = "TA_Volym"."FK_Församling"
LEFT OUTER JOIN "TA_Pagina"  
ON "TA_Volym"."ID_Volym" = "TA_Pagina"."FK_Pagina" 

[MrProgrammer]

Take a look at the documentation for the database that you're using. (Base is not a database; it's just a front end to allow OOo to interface to a database. So the database documentation is not part of OOo.) You will probably find that the allowed syntax for a left outer joined table is something like {TABLE} LEFT OUTER JOIN {TABLE} ON {JOIN-CONDITION}. So you can't simply replace the second {TABLE} with "TA_Volym", " TA_Pagina" because what you provided (two tables separated by a comma) isn't a valid {TABLE} expression.

However, you might (depending on database) find that either or both of the {TABLE} elements in the green phrase can be replaced with another left outer join. In that case you can join three tables with either
   SELECT … FROM {TABLE} LEFT OUTER JOIN {TABLE} ON … LEFT OUTER JOIN {TABLE} ON …   or
   SELECT … FROM {TABLE} LEFT OUTER JOIN {TABLE} LEFT OUTER JOIN {TABLE} ON … ON …
depending of which of the two {TABLE} elements is replaced with the second join.

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[humulus]

Thanks, now it's working. (The syntax problem was solved by your answer and I had to correct a little naming error in my code. FK_Pagina must be ID_Pagina)