Andy K Andy K - 7 months ago 10
SQL Question

Left join returning different results when changing column name

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.

Answer

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'