CODEWITHSUNDEEP
sqlsql-servert-sqlleft-joinwhere-clause
Essex
Essex

Reputation: 6128

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';

enter image description here

Table Entite

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

enter image description here

Table Archive

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

enter image description here

Table EntiteArchive

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

enter image description here

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'+'%' )

enter image description here

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)

Upvotes: 0

Views: 83

Answers (3)

CarlosSR
CarlosSR

Reputation: 1195

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

Upvotes: 1

Stavr00
Stavr00

Reputation: 3314

Is CodeEntite usable as a join?

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

Upvotes: 1

GMB
GMB

Reputation: 222462

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'+'%'

Upvotes: 2

Related Questions