SQL Request with LEFT JOIN not able to find data

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)

Answer

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)
...
```