Left join returning different results when changing column name

Question

I have the following query

SELECT COUNT(DISTINCT ETABLISSEMENTS.IU_ETS) AS compte,ETABLISSEMENTS.IU_GREFFE
FROM ENTREPRISES 
LEFT OUTER JOIN ETABLISSEMENTS ON ETABLISSEMENTS.IU_ENTREPRISE = ENTREPRISES.IU_ENTREPRISE
LEFT OUTER JOIN dbo.BASES ON dbo.ETABLISSEMENTS.IU_BASE = dbo.BASES.IU_BASE 
LEFT OUTER JOIN dbo.ETATS ON dbo.ETABLISSEMENTS.IU_ETAT = dbo.ETATS.IU_ETAT
LEFT OUTER JOIN dbo.NAF ON dbo.ETABLISSEMENTS.IU_NAF_ECO = dbo.NAF.IU_NAF 
LEFT OUTER JOIN ADRESSES ON ETABLISSEMENTS.IU_ADR_PHY = ADRESSES.IU_ADR
LEFT OUTER JOIN PARTENAIRES ON 
(PARTENAIRES.IU_PART = Etablissements.IU_GREFFE OR Etablissements.IU_GREFFE IS NULL)
WHERE (dbo.ETABLISSEMENTS.SIREN IS NOT NULL) 
AND (dbo.ETABLISSEMENTS.SIREN <> '') 
AND (dbo.ENTREPRISES.FLG_HISTORISE <> '1') 
AND (dbo.ETABLISSEMENTS.NIC IS NOT NULL) 
AND (dbo.ETABLISSEMENTS.NIC <> '') 
AND (dbo.ETABLISSEMENTS.GESTDEL = '1') 
AND (dbo.BASES.CODE = 'J1') 
AND (dbo.ETATS.LIBEL = 'Actif') 
AND (dbo.NAF.NAF NOT LIKE '000%') 
AND (dbo.ENTREPRISES.GESTDEL = '1')
AND PARTENAIRES.IU_TYPE_PART = '3'
GROUP BY ETABLISSEMENTS.IU_GREFFE

The aim is to flag the NULL and have them counted (see below).

  compte |  IU_GREFFE
  -------------------
  2      |  115
  1      |  126
  4875   |  26
  1      |  813
  21     |  2021
  36     |  5559
  6      |  149
  11661  |  27
  14904  |  130
  1      |  1298
  13402  |  25
  15790  |  NULL
  1      |  54
  11080  |  120
  9      |  423
  1      |  14

I want something neater than just having a count with a number, to have the libel like below

  compte |  Greffes
  ------------------
  2      |  Stack
  1      |  Morris
  4875   |  Dembe
  1      |  Dallas
  21     |  Delhi
  36     |  Rohintra
  6      |  Zheng
  11661  |  Liliane
  14904  |  T-shirt
  1      |  Star
  13402  |  Yes
  15790  |  NULL
  1      |  Whatsapp
  11080  |  Enkai
  9      |  Algérie
  1      |  Hewah

I change my query to have the name of the greffes, I'm interested in

SELECT COUNT(DISTINCT ETABLISSEMENTS.IU_ETS) AS compte,PARTENAIRES.LIBEL AS Greffes 
-- changing the ETABLISSEMENTS.IU_GREFFE to PARTENAIRES.LIBEL
FROM ENTREPRISES 
LEFT OUTER JOIN ETABLISSEMENTS ON ETABLISSEMENTS.IU_ENTREPRISE = ENTREPRISES.IU_ENTREPRISE
LEFT OUTER JOIN dbo.BASES ON dbo.ETABLISSEMENTS.IU_BASE = dbo.BASES.IU_BASE 
LEFT OUTER JOIN dbo.ETATS ON dbo.ETABLISSEMENTS.IU_ETAT = dbo.ETATS.IU_ETAT
LEFT OUTER JOIN dbo.NAF ON dbo.ETABLISSEMENTS.IU_NAF_ECO = dbo.NAF.IU_NAF 
LEFT OUTER JOIN ADRESSES ON ETABLISSEMENTS.IU_ADR_PHY = ADRESSES.IU_ADR
LEFT OUTER JOIN PARTENAIRES 
ON (PARTENAIRES.IU_PART = Etablissements.IU_GREFFE OR Etablissements.IU_GREFFE IS NULL)
WHERE (dbo.ETABLISSEMENTS.SIREN IS NOT NULL) 
AND (dbo.ETABLISSEMENTS.SIREN <> '') 
AND (dbo.ENTREPRISES.FLG_HISTORISE <> '1') 
AND (dbo.ETABLISSEMENTS.NIC IS NOT NULL) 
AND (dbo.ETABLISSEMENTS.NIC <> '') 
AND (dbo.ETABLISSEMENTS.GESTDEL = '1') 
AND (dbo.BASES.CODE = 'J1') 
AND (dbo.ETATS.LIBEL = 'Actif') 
AND (dbo.NAF.NAF NOT LIKE '000%') 
AND (dbo.ENTREPRISES.GESTDEL = '1')
AND PARTENAIRES.IU_TYPE_PART = '3'
GROUP BY PARTENAIRES.LIBEL

Instead of giving me the desired result, I have the result below (just giving you a tiny sample of the results as there are 284 lines )

   compte    |Greffes
   -------------------
   15790     |Mooshi
   15790     |Bazoo
   15790     |NouYawk
   15790     |Matamata

Thinking about it, the issue most likely lies with the null part as I have added this extra clause on the

LEFT OUTER JOIN PARTENAIRES ON 
(PARTENAIRES.IU_PART = Etablissements.IU_GREFFE OR Etablissements.IU_GREFFE IS NULL)

And the part below most specifically

 OR Etablissements.IU_GREFFE IS NULL

And looking at the results, 15790 is the null part.

I'm not sure what I can do to sort that out as I'm already using a LEFT OUTER JOIN to retrieve the NULL

As another variation, I tried to put the 2nd query into a nested query and do a join with the partenaires table but it gives me the same issue.

Any insights are more than welcomed

Thanks

update: Thinking about it, I can use a case to manually give the iu_greffe a proper libel but still, I would like to be able to sort the issue without going too much into manual tweaking. And to be honest, I hate being stuck into something I cannot resolve.

what about full outer join on last table and without null condition? — nada, May 10 '16 at 14:37
Hi @nada, nope it does not do the trick and i've done that too -_- — Andy K, May 10 '16 at 14:39
You have conditions in the WHERE clause that are from tables in the LEFT OUTER JOINS. That essentially makes the join into just JOIN. Move the condition to the JOIN : LEFT OUTER JOIN dbo.BASES ON dbo.ETABLISSEMENTS.IU_BASE = dbo.BASES.IU_BASE AND (dbo.BASES.CODE = 'J1') — jim31415, May 10 '16 at 14:47
Hi @jim31415 now we are talking. :) I did what you advised but nope, it does not do the trick — Andy K, May 10 '16 at 14:48

score 1 · Accepted Answer · answered May 10 '16 at 14:44

You use with clause

with t as
(
SELECT COUNT(DISTINCT ETABLISSEMENTS.IU_ETS) AS compte,ETABLISSEMENTS.IU_GREFFE
FROM ENTREPRISES 
LEFT OUTER JOIN ETABLISSEMENTS ON ETABLISSEMENTS.IU_ENTREPRISE = ENTREPRISES.IU_ENTREPRISE
LEFT OUTER JOIN dbo.BASES ON dbo.ETABLISSEMENTS.IU_BASE = dbo.BASES.IU_BASE 
LEFT OUTER JOIN dbo.ETATS ON dbo.ETABLISSEMENTS.IU_ETAT = dbo.ETATS.IU_ETAT
LEFT OUTER JOIN dbo.NAF ON dbo.ETABLISSEMENTS.IU_NAF_ECO = dbo.NAF.IU_NAF 
LEFT OUTER JOIN ADRESSES ON ETABLISSEMENTS.IU_ADR_PHY = ADRESSES.IU_ADR
LEFT OUTER JOIN PARTENAIRES ON 
(PARTENAIRES.IU_PART = Etablissements.IU_GREFFE OR Etablissements.IU_GREFFE IS NULL)
WHERE (dbo.ETABLISSEMENTS.SIREN IS NOT NULL) 
AND (dbo.ETABLISSEMENTS.SIREN <> '') 
AND (dbo.ENTREPRISES.FLG_HISTORISE <> '1') 
AND (dbo.ETABLISSEMENTS.NIC IS NOT NULL) 
AND (dbo.ETABLISSEMENTS.NIC <> '') 
AND (dbo.ETABLISSEMENTS.GESTDEL = '1') 
AND (dbo.BASES.CODE = 'J1') 
AND (dbo.ETATS.LIBEL = 'Actif') 
AND (dbo.NAF.NAF NOT LIKE '000%') 
AND (dbo.ENTREPRISES.GESTDEL = '1')
AND PARTENAIRES.IU_TYPE_PART = '3'
GROUP BY ETABLISSEMENTS.IU_GREFFE
)
select t.compte, PARTENAIRES.LIBEL AS Greffes
from t
LEFT OUTER JOIN PARTENAIRES 
ON (PARTENAIRES.IU_PART = t.IU_GREFFE)
where PARTENAIRES.IU_TYPE_PART = '3'

Hi @Proggear, you are the man! Can you tell me more about what you've done, please? Your code is working but you will need to remove the `where PARTENAIRES.IU_TYPE_PART = '3'` — Andy K, May 10 '16 at 14:58
with clause let's you use query as part of bigger one. Here result of query inside with t as ( .. ) used to make next query. You can think about t as table with result of inner select. — Proggear, May 10 '16 at 15:04

score 1 · Answer 2 · answered May 10 '16 at 15:04

When you are tired, you are messing up. This is what I've done earlier and I thought as not working

SELECT t.compte, PARTENAIRES.LIBEL AS Greffes
FROM 
(
SELECT COUNT(DISTINCT ETABLISSEMENTS.IU_ETS) AS compte,ETABLISSEMENTS.IU_GREFFE
FROM ENTREPRISES 
LEFT OUTER JOIN ETABLISSEMENTS ON ETABLISSEMENTS.IU_ENTREPRISE = ENTREPRISES.IU_ENTREPRISE
LEFT OUTER JOIN dbo.BASES ON dbo.ETABLISSEMENTS.IU_BASE = dbo.BASES.IU_BASE 
LEFT OUTER JOIN dbo.ETATS ON dbo.ETABLISSEMENTS.IU_ETAT = dbo.ETATS.IU_ETAT
LEFT OUTER JOIN dbo.NAF ON dbo.ETABLISSEMENTS.IU_NAF_ECO = dbo.NAF.IU_NAF 
LEFT OUTER JOIN ADRESSES ON ETABLISSEMENTS.IU_ADR_PHY = ADRESSES.IU_ADR
LEFT OUTER JOIN PARTENAIRES ON 
(PARTENAIRES.IU_PART = Etablissements.IU_GREFFE OR Etablissements.IU_GREFFE IS NULL)
WHERE (dbo.ETABLISSEMENTS.SIREN IS NOT NULL) 
AND (dbo.ETABLISSEMENTS.SIREN <> '') 
AND (dbo.ENTREPRISES.FLG_HISTORISE <> '1') 
AND (dbo.ETABLISSEMENTS.NIC IS NOT NULL) 
AND (dbo.ETABLISSEMENTS.NIC <> '') 
AND (dbo.ETABLISSEMENTS.GESTDEL = '1') 
AND (dbo.BASES.CODE = 'J1') 
AND (dbo.ETATS.LIBEL = 'Actif') 
AND (dbo.NAF.NAF NOT LIKE '000%') 
AND (dbo.ENTREPRISES.GESTDEL = '1')
AND PARTENAIRES.IU_TYPE_PART = '3'
GROUP BY ETABLISSEMENTS.IU_GREFFE
) AS t
LEFT OUTER JOIN PARTENAIRES 
ON (PARTENAIRES.IU_PART = t.IU_GREFFE)

But it actually does ...

Thanks to @proggear for his answer

score 1 · Answer 3 · edited May 10 '16 at 15:20

1

A. If you set conditions other than is null on join table then left join doesn't make sense.

LEFT OUTER JOIN PARTENAIRES ON (PARTENAIRES.IU_PART = Etablissements.IU_GREFFE OR Etablissements.IU_GREFFE IS NULL)
...
AND PARTENAIRES.IU_TYPE_PART = '3'

B. Run two queries to better understand what you have

LEFT OUTER JOIN PARTENAIRES ON (PARTENAIRES.IU_PART = Etablissements.IU_GREFFE)
LEFT OUTER JOIN PARTENAIRES ON (Etablissements.IU_GREFFE IS NULL)

I bet (1) will return empty result and (2) 15790 in all rows.

edited May 10 '16 at 15:20

Andy K

4,944
10
53
82

answered May 10 '16 at 15:13

Alex Kudryashev

9,120
3
27
36

Hi @Alex-kudryashev, `1` gives me 15790 in all rows and `2` gives me something similar to 1, not in all rows but in almost all of them. Gordon Linoff provided this very good answer -> http://stackoverflow.com/questions/14366004/sql-server-join-missing-null-values – Andy K May 10 '16 at 15:29

score 0 · Answer 4 · answered May 10 '16 at 14:41

Try wrapping the original query and change the column name on the outer one.

select compte, IU_GREFFE as Greffes from (
    SELECT COUNT(DISTINCT ETABLISSEMENTS.IU_ETS) AS compte,ETABLISSEMENTS.IU_GREFFE
    FROM ENTREPRISES 
    LEFT OUTER JOIN ETABLISSEMENTS ON ETABLISSEMENTS.IU_ENTREPRISE = ENTREPRISES.IU_ENTREPRISE
    LEFT OUTER JOIN dbo.BASES ON dbo.ETABLISSEMENTS.IU_BASE = dbo.BASES.IU_BASE 
    LEFT OUTER JOIN dbo.ETATS ON dbo.ETABLISSEMENTS.IU_ETAT = dbo.ETATS.IU_ETAT
    LEFT OUTER JOIN dbo.NAF ON dbo.ETABLISSEMENTS.IU_NAF_ECO = dbo.NAF.IU_NAF 
    LEFT OUTER JOIN ADRESSES ON ETABLISSEMENTS.IU_ADR_PHY = ADRESSES.IU_ADR
    LEFT OUTER JOIN PARTENAIRES ON 
    (PARTENAIRES.IU_PART = Etablissements.IU_GREFFE OR Etablissements.IU_GREFFE IS NULL)
    WHERE (dbo.ETABLISSEMENTS.SIREN IS NOT NULL) 
    AND (dbo.ETABLISSEMENTS.SIREN <> '') 
    AND (dbo.ENTREPRISES.FLG_HISTORISE <> '1') 
    AND (dbo.ETABLISSEMENTS.NIC IS NOT NULL) 
    AND (dbo.ETABLISSEMENTS.NIC <> '') 
    AND (dbo.ETABLISSEMENTS.GESTDEL = '1') 
    AND (dbo.BASES.CODE = 'J1') 
    AND (dbo.ETATS.LIBEL = 'Actif') 
    AND (dbo.NAF.NAF NOT LIKE '000%') 
    AND (dbo.ENTREPRISES.GESTDEL = '1')
    AND PARTENAIRES.IU_TYPE_PART = '3'
    GROUP BY ETABLISSEMENTS.IU_GREFFE
) as t

Hi @vijay, you did not understand correctly. Read my question again. I tried your solution already as I said in a `nested loop `and even with a join and it does not do what it should do — Andy K, May 10 '16 at 14:43
why do you have distinct in count instead of counting all? (in COUNT(DISTINCT ETABLISSEMENTS.IU_ETS) AS compte) — Vijay, May 10 '16 at 14:51
if I don't do a distinct, I have even more results.This is the why — Andy K, May 10 '16 at 14:52

Left join returning different results when changing column name

4 Answers4