I have a table called "contenedores", and this has a child table called "vehiencontenedor". There is a 3rd. table called "vehiculos" which are the ones to be filled in the table "vehiencontenedor". Table "vehiculos" have a field called "status".
"contenedores" has a field called "Aduana". Table "vehiculos" has also a field called Aduana.
Table "vehiencontenedor" has a lookup field called "numlote", which is the unique number of a vehiculo. I need to filter this field (numlote) with 2 criterias:
1. Not showing the vehiculos with status: "Contenedor" (Already achieved)
2. Showing only the vehiculos when the value of the field "Aduana" (from contenedor) is equal to the value of the field "Aduana" (from the vehiculo).
This is the modified SQL query for the lookup field "numlote" in the table "vehiencontenedor":
SELECT `vehiculos`.`id_vehiculo`, `vehiculos`.`numlote` FROM `vehiculos` LEFT JOIN `clientes` as clientes1 ON `clientes1`.`id_cliente`=`vehiculos`.`cliente_v` LEFT JOIN `paises` as paises1 ON `paises1`.`id_pais`=`clientes1`.`pais` LEFT JOIN `aduanas` as aduanas1 ON `aduanas1`.`id_aduana`=`clientes1`.`Aduana` LEFT JOIN `contenedores` as contenedores1 ON `contenedores1`.`aduana_con`=`clientes1`.`Aduana` WHERE `vehiculos`.`estatus` != 'Contenedor' AND `aduanas1`.`id_aduana` = `contenedores1`.`aduana_con` ORDER BY 2
As mentioned above, only the criteria for estatus is functioning. Because I do not know how to pass the value of the field "Aduana" in "contenedores" to acommplish the second critiria.