How can I do a query with subselect

Question

I got a problem in my query, that I think that can be resolved with a subselect.

I have a table called San_Proposta that has a primary key called Proposta_Id. In this table (San_Proposta) I have some columns, but a specific column is called StatusProposta_Id. StatusProposta_Id can only has 2 values: 1 or 2. If SanProposta_Id is 1, then the Proposta_Id don't exists in a table called San_Negocio that has a foreign key called Proposta_Id. If StatusProposta_Id is 2, then the Proposta_Id exists in my table called San_Negocio.

In San_Negocio I have some columns, but let pay attention in 2 columns in specific: ValidaVenda and ValidaCaptacao. Both columns can only have 2 values: 1 or 0.

I want to do only one query that

• returns all data that don't exists in San_Negocio, (and how a told early, if San_Proposta.Proposta_Id is 1, then don't exist in San_Negocio),
• returns all data that exists in San_Proposta but not exists in San_Negocio (and how a told early, if San_Proposta.Proposta_Id is 2, then exists in San_Negocio)
• If the San_Proposta.Proposta_Id exists on my table San_Negocio, (to Proposta_Id exists in my San_Negocio table, the San_Proposta.StatusProposta_Id has the value 2), my column called San_Negocio.ValidaVenda can't has the same value of San_Negocio.ValidaCaptacao.

How can I do that ?

I tryied the follow query, but don't work correctly

select 
   San_Proposta.Proposta_Id 
from 
   San_Proposta
left outer join 
     (
         select 
           * 
         from 
           San_Negocio 
         where 
           Proposta_Id is not null 
           and ValidaCaptacao <> ValidaVenda
     ) AS Negocio2
      on San_Proposta.Proposta_Id = Negocio2.Proposta_Id
where 
  San_Proposta.StatusProposta_Id IN (1,2)

@Updated

San_Proposta

Proposta_Id  |  StatusProposta_Id
1               1
2               1
3               1
4               2
5               2
6               2

San_Negocio

Proposta_Id  |  ValidaVenda  | ValidaCaptacao
4               1              0
5               0              1
6               1              1

@Updated

What I Expect

Proposta_Id
1
2
3
4
5

I want that the query returns Proposta_Id 1,2,3 (because don't exist in San_Negocio) and Proposta_Id 4, 5 because they exist in San_Negocio but ValidaVenda is different then ValidaCaptacao.

Answer 1

Obviously, you did your best on English translation to help get an answer for your question. I understand the elements of the query, but don't know why / what you are specifically trying to get or exclude. In addition, why all within one query. What I am offering is one query to get all the elements if they exist or not and have just added some "flag" columns which you can use or not to restrict later.

In short, a LEFT JOIN from San_Proposta to your San_Negocio on the common ID basically removes the need of your flag column of 1 or 2 to identify if a record exists or not in the other. If it finds a match, great, no matter if a false flag setting is on file. From there, you can detect the IF() qualifier of same vs different status codes on your other columns.

select 
      SP.*,
      CASE WHEN SN.Proposta_ID IS NULL THEN "No" ELSE "Yes" END as FoundInSanNegocio       
   from 
      San_Proposta SP
         LEFT JOIN San_Negocio SN
            ON SP.Proposta_ID = SN.Proposta_ID
   WHERE
         SN.Proposta_ID IS NULL
      OR NOT SN.ValidaCaptacao = SN.ValidaVenda

The having clause will specifically EXCLUDE those that ARE found in San_Negocio AND the codes are the same value.

Now, you can scan through all the records and have all the statuses at one by the "flag" columns.

Found or not (again, doesn't matter about your flag column. If it has the key, its found on the LEFT JOIN).

If it WAS NOT found, then the "HasSameValidaCode" column would be set to "n/a" (not applicable), otherwise, it will be set to "Same" or "Different".

Answer 2

I tried to follow your thoughts about the contents and relations, but it's quite hard. So I suggest you to re-think your query.

EDIT: ok. thanks for the update.

I would use the following statement.

SELECT san_proposta.proposta_id FROM   san_proposta WHERE  statusproposta_id = 1
UNION ALL
SELECT san_proposta.proposta_id FROM   san_proposta
       JOIN san_negocio ON san_proposta.proposta_id = san_negocio.proposta_id
                       AND san_negocio.validacaptacao <> san_negocio.validavenda  

Answer 3

select p.* from San_Proposta p 
where p.StatusProposta_Id = 1

union 

select p.* from San_Proposta p 
    left join San_Negocio n on n.Proposta_Id = p.Proposta_Id
where p.StatusProposta_Id = 2
    and n.ValidaCaptacao <> n.ValidaVenda