SQL LEFT JOIN and GROUP BY

Question

I have a table T_DONNE, it is just the type how the data is imported. Noid = the id Libelle = the label

NOID    LIBELLE
1       WEB
2       FTP

CLIENT:

CODE_CL   RS_NOM  VILLE      TYPE_CLIENT
85000     test    La roche   1
85001     azerty  Nantes     2
85002     qsdfg   Nantes     1

LETTRE_VOIT_FINAL: it is just like the item that client had commanded

  NOID  CODE_CLIENT RS_NOM   VILLE_EXP  FACTURATION   DATE_CLOTUR_REEL
  1     85000       test     La roche   2             2016-01-07 15:02:59
  2     85000       test     La roche   2             2016-01-07 15:03:59
  3     85001       azerty   Nantes     2             2016-01-07 15:04:59

What I want is:

CODE_CLIENT  RS_NOM    VILLE_EXP  LIBELLE  TOTAL
85000        test      La roche   WEB      2
85001        azerty    Nantes     FTP      1
85002        qsdfg     Nantes     WEB      0

and sql:

SELECT           
       LETTRE_VOIT_FINAL.CODE_CLIENT
      ,LETTRE_VOIT_FINAL.RS_NOM
      ,LETTRE_VOIT_FINAL.VILLE_EXP         
      ,T_DONNE.LIBELLE
      ,count (LETTRE_VOIT_FINAL.NOID) as TOTAL    
FROM LETTRE_VOIT_FINAL 
LEFT OUTER JOIN CLIENT ON CLIENT.CODE_CL  = LETTRE_VOIT_FINAL.CODE_CLIENT  
LEFT JOIN T_DONNE ON T_DONNE.NOID = CLIENT.TYPE_CLIENT

WHERE DATE_CLOTUR_REEL BETWEEN @DateDeb AND @DateFin
and FACTURATION = @FACTURATION
and STATUT_LV = 2  
group by CODE_CLIENT,VILLE_EXP,RS_NOM,LIBELLE

It does not quite work. It only shows:

  CODE_CLIENT  RS_NOM    VILLE_EXP   LIBELLE  TOTAL
    85000        test      La roche   WEB      2
    85001        azerty    Nantes     FTP      1

I think it does not work because of NULL value and count function.

I want to list all the clients and count the items that s/he had commanded. My syntax only show the client that have a command, I'm missing the client that had not commanded in my example is client 85002

I have inverted also but still got the same result:

SELECT 
    count (LETTRE_VOIT_FINAL.NOID) as TOTLV
    ,CODE_CL
    ,VILLE
    ,[RS_NOM]
    ,LIBELLE
FROM CLIENT 
LEFT JOIN LETTRE_VOIT_FINAL ON CLIENT.CODE_CL  = LETTRE_VOIT_FINAL.CODE_CLIENT  
LEFT JOIN T_DONNE ON T_DONNE.NOID = CLIENT.TYPE_CLIENT   

WHERE DATE_CLOTUR_REEL BETWEEN @DateDeb AND @DateFin
and FACTURATION = @FACTURATION
and STATUT_LV = 2  

group by CODE_CL,VILLE,RS_NOM,LIBELLE

@DateDeb datetime,@DateFin datetime,@FACTURATION int',@DateDeb='2016-01-07 12:00:00',@DateFin='2016-01-07 23:59:59',@FACTURATION=2

Answer 1

you don't need that derived table in you answer

SELECT CLIENT.CODE_CL
      ,CLIENT.RS_NOM
      ,CLIENT.VILLE  
      ,T_DONNE.LIBELLE
      ,count (LETTRE_VOIT_FINAL.NOID) as TOTLV    
 FROM CLIENT
 LEFT JOIN LETTRE_VOIT_FINAL
        on LETTRE_VOIT_FINAL.CODE_CLIENT = CLIENT.CODE_CL 
       and LETTRE_VOIT_FINAL.DATE_CLOTUR_REEL BETWEEN @DateDeb AND @DateFin
       and LETTRE_VOIT_FINAL.FACTURATION = @FACTURATION
       and LETTRE_VOIT_FINAL.STATUT_LV = 2    
 LEFT JOIN T_DONNE 
        ON T_DONNE.NOID = CLIENT.TYPE_CLIENT
where CLIENT.TYPE_CLIENT != 0
group by CLIENT.CODE_CL, CLIENT.VILLE, CLIENT.RS_NOM,LIBELLE

Answer 2

i got it, thanks you for all of your input.

  SELECT           
       CLIENT.CODE_CL
      ,CLIENT.RS_NOM
      ,CLIENT.VILLE  
      ,T_DONNE.LIBELLE
      ,count (LETTRE_VOIT_FINAL.NOID) as TOTLV    
  FROM CLIENT
 LEFT OUTER JOIN 
  (

  SELECT *
        FROM   LETTRE_VOIT_FINAL 
        WHERE DATE_CLOTUR_REEL BETWEEN @DateDeb AND @DateFin
        and FACTURATION = @FACTURATION
        and STATUT_LV = 2 

        )
   LETTRE_VOIT_FINAL ON  CLIENT.CODE_CL  = LETTRE_VOIT_FINAL.CODE_CLIENT  
  LEFT JOIN T_DONNE ON T_DONNE.NOID = CLIENT.TYPE_CLIENT

where type_client != 0

   group by CLIENT.CODE_CL,CLIENT.VILLE,CLIENT.RS_NOM,LIBELLE

Answer 3

You are referencing LETTRE_VOIT_FINAL, which I don't think you want to do. Now that you've added more information, I've rewritten the query with a subquery instead. You could probably do it with the join clause too. In any case, you can't do it on the whole query. This query produces the table you are trying to make

  Select client.code_cl
  , client.rs_nom
  , client.ville
  , t_donne.libelle
  , COUNT(l.noid) As total
  From client
  Inner join t_donne on t_donne.noid = client.type_client
  Left outer join (
      Select noid, code_client
      From lettre_voit_final 
      Where facturation = @facturation 
      and date_clotur_reel between @DateDeb and @DateEnd
      and status_lv = 2
  ) as l on l.code_client = client.code_cl
  group by client.code_cl
  , client.rs_nom
  , client.ville
  , t_donne.libelle

First the client table is joined with the label values, which will give you this:

85000   test    La roche    WEB
85001   azerty  Nantes  FTP
85002   qsdfg   Nantes  WEB

Then the left outer join is used to include all those values plus the count of rows with these values in the subselected rows of lettre_voit_final table to give you this:

85000   test    La roche    WEB 2
85001   azerty  Nantes  FTP 1
85002   qsdfg   Nantes  WEB 0

Answer 4

From your expected outcome it looks like your values are coming from the Client table, but your query (and column names) show that you're trying to get them from the Lettre_Voit_Final table. I think that's your biggest issue. By starting with the Client table you can join to that - a LEFT OUTER JOIN to make sure that you still get all rows in the Client table even if nothing exists in the other table for that row.

SELECT
    C.code_client,
    C.rs_nom,
    C.ville,
    D.libelle,
    COUNT(*)
FROM
    Client C
INNER JOIN T_Donne D ON D.noid = C.type_client
LEFT OUTER JOIN Lettre_Voit_Final LVF ON LVF.code_client = C.code_cl
GROUP BY
    C.code_client,
    C.rs_nom,
    C.ville,
    D.libelle

Some other notes...

Your column names seem to be inconsistent (for example, code_client vs. code_cl). This can really slow down development and cause other mistakes in coding.

You may have some duplication of data (although I'm not certain) with ville vs. ville_exp. Maybe one of those is a "default" value and the other an actual value or something and make sense in your database, but just something else to be careful about.

Answer 5

your query flows LETTRE_VOIT_FINAL => CLIENT => T_DONNE

but you use left join records (rows) in table CLIENT which can't be joined (i.e. - does not a have corresponding key stated by the join clause) will not be taken to the result set. change it to full join / right join or change the order of the tables join

SELECT 
      count (LETTRE_VOIT_FINAL.NOID as TOTLV

      ,[CODE_CLIENT]
      ,VILLE_EXP
      ,[RS_NOM]
      ,LIBELLE
  FROM [LETTRE_VOIT_FINAL] 
  FULL OUTER JOIN CLIENT ON CLIENT.CODE_CL  = LETTRE_VOIT_FINAL.CODE_CLIENT  
  FULL OUTER T_DONNE ON T_DONNE.NOID = CLIENT.TYPE_CLIENT
  where 
 DATE_CLOTUR_REEL BETWEEN @DateDeb AND @DateFin
  and FACTURATION = @FACTURATION
   and STATUT_LV = 2  
   group by CODE_CLIENT,VILLE_EXP,RS_NOM,LIBELLE