0
Good afternoon, Folks!
I’m having a hard time building a select that is filtered based on a data contained in another table.
To contextualize: It is a warehouse management system (WMS) and need to bring as a result of a query all waves (loads) that are in separation process, for this there are 2 tables to help me:
Table "saidapornf" has the fields "idonda" which is the load identifier and "psepiniciada" which returns me from 0 to 100 the current percentage of separation of that load.
Through the table above I can know exactly what I need, which cargo is in separation, however, I need to make a filter based on the "Warehouse" since within this system we have several warehouses and I want to bring the loads in separation only from a specific warehouse (warehouse of id 5).
For this, there is the table "romaneiopai" which also contains the column "idonda" as well as the table above and the field "idarmazem" which brings just the warehouse which that wave (or charge) belongs to.
So I built the following select:
SELECT v_saidapornf.idonda, v_saidapornf.psepiniciada, romaneiopai.idarmazem
FROM v_saidapornf, romaneiopai
WHERE romaneiopai.idarmazem = 5 and v_saidapornf.psepiniciada BETWEEN 1 and 99
And the result was this:
Notice that in fact he only brought me the warehouse waves = 5, however, there are waves in this result that do not belong to warehouse 5, he brought only because they are also in separation, but belong to another warehouse (such as wave 4218 belongs to warehouse 40) and in the result of this select is populated with warehouse number 5.
How could I make this query to really bring me only the waves belonging to warehouse 5 staff?
Thank you all very much!!
Without passing the DDL’s on the table, it’s hard to judge, but what you’re doing is a Cartesian product. You should pass the JOIN condition (column) in the command
FROM. By the description of the problem what you need is aINNER JOIN.– Danizavtz
FROM v_saidapornf, romaneiopaihere are two tables, to use more than one table you need to JOIN between them, otherwise the database does not know how to relate and will make a "cartesiado" of the tables. As @Danizavtz commented, JOIN between these two tables– Ricardo Pontual
Obrigado pelas respostas pessoal, mas consegui resolver o problema sem fazer o JOIN, a clausula where ficou assim " WHERE v_saidapornf.idonda = romaneiopai.idonda and romaneiopai.idarmazem = 5 and v_saidapornf.psepiniciada BETWEEN 1 and 99" - the difference is that I added v_saidapornf.idonda = romaneiopai.idonda at the beginning of the condition and it brought me only the waves that actually belong to the warehouse 5.
– Leojbnv
You did just JOIN ...
– Motta
@Motta but without using the command explicitly? I didn’t know it was possible, I am studied and have a still shallow knowledge of SQL. thanks friends!
– Leojbnv