CODEWITHSUNDEEP
sqlsql-servert-sql
Lucas_Santos
Lucas_Santos

Reputation: 4740

How can I do a LEFT OUTER JOIN select

How can I do a select that: select all the proposta_id that exists in my table called San_Proposta and all proposta_id that exists in my table San_Negocio WHEN this proposta_id in San_Negocio has the column ValidaCaptacao = 0 AND ValidaVenda = 1 or the column ValidaVenda = 0 AND ValidaCaptacao = 1 AND the column Proposta_Id in San_Negocio can't be NULL ??

I don't know if I have to use the LEFT OUTER JOIN, but follow my attempt, that doesn't works correctly, because I have some data in San_Proposta.Proposta_Id that don't exists in San_Negocio.Proposta_Id but this data doesn't appears.

    SELECT San_Proposta.Proposta_Id 
    FROM San_Proposta
    LEFT OUTER JOIN San_Negocio
    ON San_Proposta.Proposta_Id = San_Negocio.Proposta_Id
    WHERE San_Negocio.Proposta_Id IS NOT NULL
    AND (San_Negocio.ValidaCaptacao = 1 AND San_Negocio.ValidaVenda = 0) 
    OR (San_Negocio.ValidaCaptacao = 0 AND San_Negocio.ValidaVenda = 1)

Upvotes: 0

Views: 187

Answers (2)

Peter Kiss
Peter Kiss

Reputation: 9329

SELECT San_Proposta.Proposta_Id 
FROM San_Proposta
INNER JOIN San_Negocio
ON San_Proposta.Proposta_Id = San_Negocio.Proposta_Id
WHERE (San_Negocio.ValidaCaptacao = 1 AND San_Negocio.ValidaVenda = 0) 
OR (San_Negocio.ValidaCaptacao = 0 AND San_Negocio.ValidaVenda = 1)

Upvotes: -1

Joe Stefanelli
Joe Stefanelli

Reputation: 135888

When you test columns from a LEFT JOINed table in the WHERE clause, you force that join to behave as if it were an INNER JOIN. Instead, make those tests part of the join conditions. You'll also want to drop the WHERE San_Negocio.Proposta_Id IS NOT NULL as that results in an INNER JOIN as well.

SELECT San_Proposta.Proposta_Id 
    FROM San_Proposta
        LEFT OUTER JOIN San_Negocio
            ON San_Proposta.Proposta_Id = San_Negocio.Proposta_Id
                AND (San_Negocio.ValidaCaptacao = 1 AND San_Negocio.ValidaVenda = 0) 
                 OR (San_Negocio.ValidaCaptacao = 0 AND San_Negocio.ValidaVenda = 1)

Upvotes: 4

Related Questions