SQL Request with LEFT JOIN not able to find data

Question

I'm working on my C# project and I have to get data from database thanks to .sql procedures.

Synthesis :

Table ProprietesCourantes is linked to table Entite.

Table Archive is linked to table EntiteArchive

==> I would like to make a link between ProprietesCourantes and Archive thanks to field Libellé

Table ProprietesCourantes

I have several tables in my case :

SELECT TOP (1000) [IdEntite]
      ,[Libelle]
      ,[EstActif]
      , ...
      , ...
  FROM [Database].[dbo].[ProprietesCourantes]
  WHERE [Libelle] = 'DELTA SERVICE AUTO';

Table Entite

SELECT TOP (1000) [IdEntite]
      ,[TypeEntite]
      ,[CodeEntite]
      ,[TypeCodeEntite]
  FROM [Database].[dbo].[Entite]
  WHERE IdEntite = '165';

Table Archive

SELECT TOP (1000) [IdArchive]
      ,[IdEntite]
      ,[NoteFinale]
      ,[EstValide]
  FROM [Database].[dbo].[Archive]
  WHERE IdEntite = '33' and EstValide = '1';

Table EntiteArchive

SELECT TOP (1000) [IdEntite]
      ,[TypeEntite]
      ,[CodeEntite]
      ,[Libelle]
      ,...
  FROM [Database].[dbo].[EntiteArchive]

MY SQL REQUEST :

SELECT
            ProprietesCourantes.IdEntite                                                                    as IdEntite, 
            ProprietesCourantes.Libelle                                                                     as RaisonSociale,
            Entite.CodeEntite                                                                               as IdCMCIC,
            ProprietesCourantes.NomParent                                                                   as NomGN,
            Archive.NoteFinale                                                                              as Cotation,
            Archive.DateValiditeQuestionnaireDeSoutien                                                      as DateValiditeQuestionnaireDeSoutien,
            ProprietesCourantes.CodePays                                                                    as Pays,
            ProprietesCourantes.EstActif                                                                    as Statut,
            CAST(CASE WHEN Entite.CodeEntite = ProprietesCourantes.IdSocMere THEN 1 ELSE 0 END AS BIT)      as EstMaisonMere,
            ProprietesCourantes.IdCMCICParent                                                               as IdCMCICParent,
            ProprietesCourantesGN.CodeAlgo                                                                  as CodeAlgoParent

    FROM ProprietesCourantes

    LEFT JOIN Archive   ON (Archive.IdEntite  = ProprietesCourantes.IdEntite and Archive.EstValide=1)
    LEFT JOIN Entite ON Entite.IdEntite = ProprietesCourantes.IdEntite
    LEFT JOIN Entite as EntiteGN ON EntiteGN.CodeEntite = ProprietesCourantes.IdCMCICParent
    LEFT JOIN ProprietesCourantes as ProprietesCourantesGN ON ProprietesCourantesGN.IdEntite = EntiteGN.IdEntite

    WHERE Entite.TypeEntite = 1 
        AND   (ProprietesCourantes.Libelle LIKE '%'+'DELTA SERVICE'+'%' )

As you can see : Cotation and DateValiditeQuestionnaireDeSoutien are NULL because LEFT JOIN Archive ON (Archive.IdEntite = ProprietesCourantes.IdEntite can't work (different IDEntite).

I tried to replace by this, but it doesn't work, any idea ?

LEFT JOIN Archive   ON ((select EntiteArchive.Libelle from EntiteArchive LEFT JOIN Archive ON (EntiteArchive.IdEntite = Archive.IdEntite)) = ProprietesCourantes.Libelle and Archive.EstValide=1)

The condition on `entite` should be in the `on` clause, not the `where` clause. — Gordon Linoff, Feb 20 '20 at 13:52
@GordonLinoff Could you write an exemple to illustrate your sentence ? — Essex, Feb 20 '20 at 13:54
In other words, by stuffing a condition down in the WHERE clause on a table that is left-joined, you are applying it to the entire result set AFTER the joins happen. You are, essentially, turning your `LEFT OUTER JOIN` into an `INNER JOIN`. You, instead, want that filter to happen to your `entite` table BEFORE the join, so either a subquery or just moving the condition to the `ON` clause will suffice. — JNevill, Feb 20 '20 at 13:57
Does this answer your question? [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) — philipxy, Feb 20 '20 at 21:55
@philipxy No it was not this issue. I know I have to write a concise question, clear. But in my case, it's not possible to explain my issue and what I want to get without set the context and tables around. Sorry — Essex, Feb 21 '20 at 08:29
The duplicate is a problem with your code. PS You don't clearly explain what result you want or what is wrong with your code. Also: In code questions give a [mre]--cut & paste & runnable code; example input (as initialization code) with desired & actual output (including error messages); tags & versions; clear specification & explanation. For errors that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL, which includes constraints & indexes & tabular initialization. — philipxy, Feb 21 '20 at 09:27

score 2 · Answer 1 · answered Feb 20 '20 at 13:55

Just move the condition on the let joined table Entite to the on part of the join. Otherwise, it becomes a mandatory condition, and filters out records for which there is no match in Entite.

SELECT
            ProprietesCourantes.IdEntite                                                                    as IdEntite, 
            ProprietesCourantes.Libelle                                                                     as RaisonSociale,
            Entite.CodeEntite                                                                               as IdCMCIC,
            ProprietesCourantes.NomParent                                                                   as NomGN,
            Archive.NoteFinale                                                                              as Cotation,
            Archive.DateValiditeQuestionnaireDeSoutien                                                      as DateValiditeQuestionnaireDeSoutien,
            ProprietesCourantes.CodePays                                                                    as Pays,
            ProprietesCourantes.EstActif                                                                    as Statut,
            CAST(CASE WHEN Entite.CodeEntite = ProprietesCourantes.IdSocMere THEN 1 ELSE 0 END AS BIT)      as EstMaisonMere,
            ProprietesCourantes.IdCMCICParent                                                               as IdCMCICParent,
            ProprietesCourantesGN.CodeAlgo                                                                  as CodeAlgoParent    
    FROM ProprietesCourantes    
    LEFT JOIN Archive
        ON  Archive.IdEntite  = ProprietesCourantes.IdEntite 
        AND Archive.EstValide=1)
    LEFT JOIN Entite 
        ON  Entite.IdEntite = ProprietesCourantes.IdEntite 
        AND Entite.TypeEntite = 1 
    LEFT JOIN Entite as EntiteGN 
        ON  EntiteGN.CodeEntite = ProprietesCourantes.IdCMCICParent
    LEFT JOIN ProprietesCourantes as ProprietesCourantesGN 
        ON  ProprietesCourantesGN.IdEntite = EntiteGN.IdEntite
    WHERE ProprietesCourantes.Libelle LIKE '%'+'DELTA SERVICE'+'%'

Ok I understand, but it gives me always both fields to NULL and I have to find a way to JOIN tables in order to get `Cotation` and `DateValiditeQuestionnaireDeSoutien`. Maybe my question was not really clear ? `LEFT JOIN Archive ON Archive.IdEntite = ProprietesCourantes.IdEntite AND Archive.EstValide=1)` couldn't match — Essex, Feb 20 '20 at 13:57

score 1 · Accepted Answer · edited Feb 20 '20 at 14:47

1

It is not easy to see how your tables are... could it be possible that Archive table has no rows for IdEntite='165'? Are the two keys of the same format? You are showing the output for IdEntite='165' but the 4 example tables are for 2 different IdEntite. Can you provide more insights?

Edited: For what I understand, you want to join by Libelle field both ProprietesCourantes and Archive but your join is based on IdEntite?

If so, use an intermediate table join ```...

Archive.NoteFinale                                   as Cotation,
Archive.DateValiditeQuestionnaireDeSoutien           as DateValiditeQuestionnaireDeSoutien,
...
LEFT JOIN EntiteArchive ON (EntiteArchive.Libelle = ProprietesCourantes.Libelle )
LEFT JOIN Archive ON (EntiteArchive.IdEntite = Archive.IdEntite)
...
```

edited Feb 20 '20 at 14:47

Essex

6,042
11
67
139

answered Feb 20 '20 at 14:05

CarlosSR

1,145
1
10
22

Yes it's not easy to show you the database relations. But for the same object named (Libelle field) "DELTA SERVICE AUTO", it has IdEntite = '165' in ProprietesCourantes table and IdEntite = '33' in Archive table. – Essex Feb 20 '20 at 14:07
It seems to work very well ! It seems to be the good answer :) Thank you ! **EDIT** If I want to join by CodeEntite field it's possible ? – Essex Feb 20 '20 at 14:47
I changed by this and it works too : `LEFT JOIN EntiteArchive ON (EntiteArchive.CodeEntite = (SELECT CodeEntite FROM [dbo].Entite WHERE Entite.IdEntite = ProprietesCourantes.IdEntite)) LEFT JOIN Archive ON (EntiteArchive.IdEntite = Archive.IdEntite and Archive.EstValide=1)` – Essex Feb 20 '20 at 15:06

score 1 · Answer 3 · answered Feb 20 '20 at 14:08

1

Is CodeEntite usable as a join?

JOIN [EntiteArchive] ON [Entite].[CodeEntite] =  [EntiteArchive].[CodeEntite] 
JOIN [Archive] ON [EntiteArchive].[IdEntite]  = [Archive].[IdEntite]

answered Feb 20 '20 at 14:08

Stavr00

3,219
1
16
28

Yes it's what I'm trying to do. I wrote this, but it's not a good syntax, just to get the idea : `LEFT JOIN Archive ON (SELECT CodeEntite FROM EntiteArchive WHERE IdEntite = Archive.IdEntite = SELECT CodeEntite FROM Entite WHERE IdEntite = ProprietesCourantes.IdEntite)` – Essex Feb 20 '20 at 14:16

SQL Request with LEFT JOIN not able to find data

3 Answers3