Can figure out this query

Question

OK I have the following tables

+-------------------+
|      FMdonnee     |
+-------------------+
| uGuid             |
| ...               |
| uGuid_FMchampForm |
| uGuid_FMdoc       |
+-------------------+

+-------------------+
|   FMchampForm     |
+-------------------+
| uGuid             |
| ...               |
| uGuid_FMgroup     |
| uGuid_FMsection   |
+-------------------+

+-------------------+
|      FMgroup      |
+-------------------+
| uGuid             |
| ...               |
+-------------------+

uGuid is the primary key of all table and it's type is uniqueidentifier

FMchampForm will alway have data but it's foreign key uGuid_FMgroup might be null. There might be some data in the table FMdonnee. I'm trying to get all the data from FMchampForm that match the corresponding FMsection (fk of FMchampform) and if there are data in FMdonnee or FMgroup attached to this entry of FMchampForm I want to pick them up.

I tryed the following. Witch is almost good except it won't take the FMchampForm entry if there is no data attached from FMdonnee

SELECT  cf.cNom                     nom_cf
          , cf.iPosition                pos_cf
          , cf.cTypeValeur              typVal_cf
          , cf.bActif                   actif_cf
          , cf.cCommentaire             com_cf
          , cf.bLabelVisible            lblVisible_cf
          , cf.cPadding                 pad_cf
          , cf.bMultiLines              multiLines_cf
          , cf.bStartLine               startLine_cf
          , g.cNom                      nom_g
          , g.iPosition                 pos_g
          , g.iNbRepTemplate            nbRepTemplate_g
          , g.cCommentaire              com_g
          , g.cPadding                  pad_g
          , g.bLabelVisible             lblVisible_g
          , g.bIsBreakable              isBreakable_g
          , d.cValeur                   valeur_d
          , d.cGroupe                   groupe_d
          , d.uGuid_FMunite             guidUnite_d
FROM FMdonnee d, FMchampForm cf
LEFT JOIN FMgroup g ON cf.uGuid_FMgroup = g.uGuid
WHERE cf.uGuid_FMsection = @guidFMsection
AND d.uGuid_FMchampForm = cf.uGuid
AND d.uGuid_FMdoc = @guidFMdoc
ORDER BY g.iPosition ASC, d.cGroupe ASC, cf.iPosition ASC, cf.cNom ASC

I think I need the LEFT JOIN the FMdonnee but I don't know how to join it also on FMchampForm. I hope I am clear enought to get you understanding what I'm trying to do

Answer 1

Since FMdonnee records may not exist, you need to LEFT JOIN from FMchampForm to FMdonnee. Try:

SELECT  cf.cNom                     nom_cf
          , cf.iPosition                pos_cf
          , cf.cTypeValeur              typVal_cf
          , cf.bActif                   actif_cf
          , cf.cCommentaire             com_cf
          , cf.bLabelVisible            lblVisible_cf
          , cf.cPadding                 pad_cf
          , cf.bMultiLines              multiLines_cf
          , cf.bStartLine               startLine_cf
          , g.cNom                      nom_g
          , g.iPosition                 pos_g
          , g.iNbRepTemplate            nbRepTemplate_g
          , g.cCommentaire              com_g
          , g.cPadding                  pad_g
          , g.bLabelVisible             lblVisible_g
          , g.bIsBreakable              isBreakable_g
          , d.cValeur                   valeur_d
          , d.cGroupe                   groupe_d
          , d.uGuid_FMunite             guidUnite_d
FROM FMchampForm cf
LEFT JOIN FMdonnee d 
       ON d.uGuid_FMchampForm = cf.uGuid AND d.uGuid_FMdoc = @guidFMdoc
LEFT JOIN FMgroup g ON cf.uGuid_FMgroup = g.uGuid
WHERE cf.uGuid_FMsection = @guidFMsection
ORDER BY g.iPosition ASC, d.cGroupe ASC, cf.iPosition ASC, cf.cNom ASC

(As a side note, it's a bad idea to mix implicit and explicit join syntax in the same query, as it can be particularly confusing.)