Problems with FULL OUTER JOIN (Error 28)

English
#1

Hello,

I’m having some problems with FULL OUTER JOIN when doing a SQL query in LibreOffice Base.
I’m trying to do a full join between two tables. tbl_VEHICULOS (cars) and tbl_ZONAS_VEHICULOS (neighborhoods) where some vehicles are assigned.

You can download the database here:

https://mega.co.nz/#!hJAGwToT!Rh2VvcqLROGJmca65wQy7I-hpNQwYjQy4o-duWbjCLI

If I do a INNER JOIN, this code works fine.
But with an INNER JOIN I see only those records coincident, involved in the query.

However, let’s imagine I want to do a FULL OUTER JOIN so I can join both tables, so I would be able to see those records involved (conincident) and those that are not coincident, showing the blank spaces.

As you can see the field that is linking both tables is ID_Vehiculo

If I type this code, I get an error.

SELECT "tbl_VEHICULOS"."ID_Vehiculo", 
     "tbl_VEHICULOS"."Marca", 
     "tbl_VEHICULOS"."Modelo", 
     "tbl_ZONAS_Y_VEHICULOS"."ZonaAdministrativa", 
     "tbl_ZONAS_Y_VEHICULOS"."Poblacion", 
     "tbl_ZONAS_Y_VEHICULOS"."Provincia" 

FROM 
   "tbl_ZONAS_Y_VEHICULOS"

FULL OUTER JOIN "tbl_VEHICULOS" 

ON "tbl_ZONAS_Y_VEHICULOS"."ID_Vehiculo" = "tbl_VEHICULOS"."ID_Vehiculo"

SQL Status: S0022

Error code: -28

Column not found: tbl_VEHICULOS.ID_Vehiculo in statement [SELECT “tbl_VEHICULOS”.“ID_Vehiculo”,
“tbl_VEHICULOS”.“Marca”,
“tbl_VEHICULOS”.“Modelo”,
“tbl_ZONAS_Y_VEHICULOS”.“ZonaAdministrativa”,
“tbl_ZONAS_Y_VEHICULOS”.“Poblacion”,
“tbl_ZONAS_Y_VEHICULOS”.“Provincia”

FROM
“tbl_ZONAS_Y_VEHICULOS”

FULL OUTER JOIN “tbl_VEHICULOS”

ON “tbl_ZONAS_Y_VEHICULOS”.“ID_Vehiculo” = “tbl_VEHICULOS”.“ID_Vehiculo”
]

**Any idea of what am I doing wrong?

Any help would be greatly appreciated.**

Cheers

#2

I’d report this to the devs, this issue is fishy: https://bugs.documentfoundation.org/enter_bug.cgi?product=LibreOffice

#3

I found it also strange, however I liked to ask because I’m not very skilled in HSQLDB, however, if a INNER JOIN is working fine between two tables, a FULL OUTER JOIN or FULL JOIN should also retrieve results, and here we are having an error. I don’t understand why. I just wanted to know if this was my fault or a LibreOffice Base error.
Have you reported it? Please, can you give me the link to follow the bug number?
Cheers

#4

@mrmister: it sounds like a bug in the HSQLDB itself cuz it says no such column found when there’s nothing wrong with the code. If it was syntax violation, it would say so. Oddly enough, there’s no mention about FULL OUTER JOIN in any of HSQLDB’s docs. Worse yet, its docs totally suck.

#5

Hi - For this I would do:

SELECT "tbl_VEHICULOS"."ID_Vehiculo", "tbl_VEHICULOS"."Marca", "tbl_VEHICULOS"."Modelo", 
"tbl_ZONAS_Y_VEHICULOS"."ZonaAdministrativa",  "tbl_ZONAS_Y_VEHICULOS"."Poblacion", 
"tbl_ZONAS_Y_VEHICULOS"."Provincia" 
FROM "tbl_VEHICULOS" 
LEFT JOIN  "tbl_ZONAS_Y_VEHICULOS" 
ON "tbl_ZONAS_Y_VEHICULOS"."ID_Vehiculo" = "tbl_VEHICULOS"."ID_Vehiculo"

HTH - Regards

#6

Hi Pierre, yes the LEFT JOIN is working fine. My doubt exactly was about the FULL JOIN. Is it working for you? I think this is an issue from LibreOffice.

#7

According to hsqldb documentation, FULL JOIN is not supported with this version, so the limitation is there…

#8

Seems it works fine with RIGHT JOIN.

SELECT "tbl_VEHICULOS"."ID_Vehiculo", 
     "tbl_VEHICULOS"."Marca", 
     "tbl_VEHICULOS"."Modelo", 
     "tbl_ZONAS_Y_VEHICULOS"."ZonaAdministrativa", 
     "tbl_ZONAS_Y_VEHICULOS"."Poblacion", 
     "tbl_ZONAS_Y_VEHICULOS"."Provincia" 
FROM    "tbl_ZONAS_Y_VEHICULOS"
RIGHT JOIN "tbl_VEHICULOS" ON "tbl_ZONAS_Y_VEHICULOS"."ID_Vehiculo" = "tbl_VEHICULOS"."ID_Vehiculo"
#9

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.