How to list NULL on a LEFT JOIN even if it returns joins?

Asked

Viewed 657 times

4

The situation found was that:

I am making a junction between representatives and sellers, and in another situation this junction is made with another sales. There are sales without sellers but are related to the representative, and I want to capture and verify the failure of this relationship in these sales.

Problem:

I’m making a LEFT JOIN And to some extent he meets me according to the very limitation of the clause. Only I need that when I make a relationship between the representative and the sellers, even if he finds some of them, also return me a NULL.

Current example:

SELECT r.cod as cod_representante, v.cod_vendedor 
FROM representantes r 
LEFT JOIN vendedores v 
ON v.cod_representante = r.cod

It returns to me something like :

cod_representante | cod_vendedor
1                 | 1
1                 | 2
2                 | NULL
3                 | NULL
4                 | 3

The desired result would be something like:

cod_representante | cod_vendedor
1                 | 1
1                 | 2
1                 | NULL
2                 | NULL
3                 | NULL
4                 | 3
4                 | NULL
sql sql-server

  • you want, for each cod_representative to have a corresponding NULL in cod_seller, even if cod_seller has a corresponding value?

  • Exactly, @Gokussjgod. Even if it has values in the merge, I want to increment one more value, being NULL.

  • Quite strange your need, but I believe you have to make one more union of your tables, perhaps being in a subselect. Could post your tables ?

  • Yes, it is strange but it was the temporary solution I saw and that immediately meets this problem I described.

  • It worked the way I posted it?

  • 1

    Yes, I did, making some adaptations but it served me well.

Show 1 more comment

2 answers

2

Use the INNER JOIN

SELECT 
   r.cod as cod_representante, 
   v.cod_vendedor 
FROM 
   representantes r 
INNER JOIN vendedores v ON v.cod_representante = r.cod
  • 1

    That way the nulls don’t even come.

  • Thus neither representatives without sellers will be listed, Raphael. What I need is to "increment" a NULL even listing sellers.

1


See if that’s it.

declare @representantes table
(
   cod int
)


declare @vendedores table
(
cod_vendedor int,
cod_representante int
)

insert into @representantes values
(1),
(2),
(3),
(4)

insert into @vendedores values
(1,1),
(2,1),
(3,4)


select * from
(
    SELECT r.cod as cod_representante, v.cod_vendedor 
    FROM @representantes r 
    JOIN @vendedores v 
    ON v.cod_representante = r.cod
    union all
    select * from
    (
        select r1.cod as cod_representante, v1.cod_vendedor 
        from  @vendedores v1
        full outer join @representantes r1
        on v1.cod_vendedor is null
    )d
    where d.cod_representante is not null
) d
order by cod_representante, CASE WHEN cod_vendedor IS NULL THEN 1 ELSE 0 END

inserir a descrição da imagem aqui

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.