I have made a query that only obtains the account numbers with their balance if they appear in the table saldos
. At first it was my goal, which is now the opposite, that is, to be able to obtain the account numbers that do not have a balance in the table saldos
.
The query tables are as follows:
MARKING (this table has the account numbers of specific clients, such as specific id for managers and channels).
BALANCES (this table contains the balances that customers have for
id
. If the customer does not have a balance, it does not appear in the table).CLIENTS (this table has the information of all the clients with their account number, I relate it to the table
marcaje
by account number, since as I explained in the tablemarcaje
it contains only specific clients (which are the ones that really matter to me), also I relate it to the tablesaldos
byid
, since the tablesaldos
can only be related byid
to the tableclientes
).MANAGER (this table has the
id
number of managers needed to perform the query).CHANNEL (this table has the
id
number of channels needed to perform the query).
Query #1 , this allows you to obtain the account numbers necessary to obtain their balances . (returns 422 account number found in the table marcaje
).
select
M.NUMERO_DE_CUENTA as cuentas
from MARCAJE M
inner join CLIENTES C on C.NUMERO_DE_CUENTA = M.NUMERO_DE_CUENTA
inner join GERENTE G on G.ID = M.ID_GERENTE
inner join CANAL CN on CN.ID = M.ID_CANAL
where CN.ID = '2' and G.ID = '41';
Query #2 , this allows to obtain those saldos
of the accounts found in marcaje
, not only those filtered from the first query, but all the accounts.
select
C.NUMERO_DE_CUENTA as cuentas
,S.SALDO as saldo_disponible
from CLIENTES C
inner join SALDOS S on S.ID = C.ID
inner join MARCAJE M on M.NUMERO_DE_CUENTA = C.NUMERO_DE_CUENTA
Query #3, this is the relationship between the first and second query, it obtains the accounts from the first query that have a balance in the second query (they return 176 account numbers that they have saldo
, 247 account numbers are missing, which are the ones that do not have any type of saldo
).
with
gerente as (
select
M.NUMERO_DE_CUENTA as cuentas
from MARCAJE M
inner join CLIENTES C on C.NUMERO_DE_CUENTA = M.NUMERO_DE_CUENTA
inner join GERENTE G on G.ID = M.ID_GERENTE
inner join CANAL CN on CN.ID = M.ID_CANAL
where CN.ID = '2' and G.ID = '41'
),
saldos as (
select
C.NUMERO_DE_CUENTA as cuentas
,S.SALDO as saldo_disponible
from CLIENTES C
inner join SALDOS S on S.ID = C.ID
inner join MARCAJE M on M.NUMERO_DE_CUENTA = C.NUMERO_DE_CUENTA
)
select
g.cuentas,
s.saldo_disponible
from gerente g, saldos s
where g.cuentas = s.cuentas;
As I said at the beginning, my goal is to get those 247 account numbers that do not have saldo
, the only thing that occurred to me was to use the function not exists
but it did not work.
Thank you in advance for reading this and trying to help me with this problem.
To do the opposite of what you have, you don't have to change your code much.
We know that with an inner join it brings the ones from both sides... but if we want the ones from only one side we can do a left/right join and nullify the other side... that way, it would only bring the ones that don't have a relationship in the other side.