Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This is my SQL request :

SELECT COUNT( b0_.id ) AS sclr0, d1_.codeLieu AS codeLieu1, d1_.nomLieu AS nomLieu2,   
      d1_.lngLieu AS lngLieu3, d1_.latLieu AS latLieu4, 
      m2_.libelleMention AS libelleMention5, t3_.libelleType AS libelleType6
FROM Bac b0_
INNER JOIN Bachelier b4_ ON b0_.bachelier_cne = b4_.cne
INNER JOIN EtablissementBac e5_ ON b0_.etabBac_id = e5_.codeLieu
INNER JOIN Delegation d1_ ON e5_.delegation_id = d1_.codeLieu
INNER JOIN MentionBac m2_ ON b0_.mentionBac_id = m2_.codeMention
INNER JOIN TypeBac t3_ ON b0_.typeBac_id = t3_.codeType
WHERE m2_.codeMention IN ('TB',  'B')
AND t3_.codeType IN ('114',  '129')
GROUP BY d1_.codeLieu, m2_.codeMention, m2_.libelleMention, 
         t3_.codeType, t3_.libelleType, t3_.abbrType
ORDER BY d1_.codeLieu ASC , b0_.mentionBac_id ASC , b0_.typeBac_id ASC

And this is the result (it's a screenshot):

http://nsa33.casimages.com/img/2013/05/10//130510034126658849.png

The thing is I get all the rows with the equivalent number (sclr0), but I also need rows with null values. The needed rows are defined by the columns libelleMention5, libelleType6. In other words, I want for each codeLieu1, all the possible combinations of libelleMention5 and libelleType6, and the equivalent number in the column sclr0 (which should be 0 if the cell is NULL).

How can I do that ?

EDIT

This is the new screenshot : http://nsa34.casimages.com/img/2013/05/11//130511101655302101.png

share|improve this question
    
If you want the other question you have posted 4 hours ago, to be closed, please flag it so moderators can close it (or migrate it here): MySQL : INNER JOIN & COUNT : I need to get results that are NULL The SE network suggests questions to be asked in 1 site only, not cross-posted. –  ypercube May 10 '13 at 13:46
    
Alright. I did. –  Sherlock May 10 '13 at 13:47
1  
Can you add the relationships between the tables or the CREATE TABLE statements? –  ypercube May 10 '13 at 13:58
1  
possible duplicate of MySQL : INNER JOIN & COUNT : I need to get results that are NULL –  ypercube May 10 '13 at 17:11
    
I don't have the CREATE TABLE statements.. The relationships between tables are the ones as above in the INNER JOIN clauses.. I just want to know how to do it.. You can explain to me using an example of yours.. but with three or four tables (an analogy) =) –  Sherlock May 10 '13 at 17:34
show 3 more comments

1 Answer

up vote 1 down vote accepted

OK, without any detailed info about the tables' relationships (1-1, 1-to-many, many-to-many) and the primary keys, it's not easy to deal with this. But lets try.

The FROM clause has a 6-table join:

FROM 
  Bac              b0_  INNER JOIN 
  Bachelier        b4_   ON b0_.bachelier_cne = b4_.cne INNER JOIN 
  EtablissementBac e5_   ON b0_.etabBac_id = e5_.codeLieu INNER JOIN 
  Delegation       d1_   ON e5_.delegation_id = d1_.codeLieu INNER JOIN 
  MentionBac       m2_   ON b0_.mentionBac_id = m2_.codeMention INNER JOIN 
  TypeBac          t3_   ON b0_.typeBac_id = t3_.codeType

Since 2 tables (m2_ and t3_) are involved in the WHERE clause:

WHERE m2_.codeMention IN ('TB',  'B')
  AND t3_.codeType IN ('114',  '129')

and one more (d1_) in the GROUP BY clause:

GROUP BY 
  d1_.codeLieu, 
  m2_.codeMention, m2_.libelleMention, 
  t3_.codeType, t3_.libelleType, t3_.abbrType

we'll assume that these 3 should be join with proper INNER joins.

And since you are counting on b0_, this will probably have to be "LEFT" joined. That leaves with some choices about the other two tables (b4_ and e5_). So, lets rewrite the FROM part. First the 3 (sure JOIN) tables, then the ambiguous 2 and lastly the sure (LEFT JOIN) b0_:

FROM 
  TypeBac          t3_  INNER JOIN 
  MentionBac       m2_  INNER JOIN 
  Delegation       d1_  INNER JOIN 

  EtablissementBac e5_   ON e5_.delegation_id = d1_.codeLieu INNER JOIN 
  Bachelier        b4_  
                        LEFT JOIN
  Bac              b0_   ON b0_.bachelier_cne = b4_.cne 
                        AND b0_.etabBac_id = e5_.codeLieu 
                        AND b0_.typeBac_id = t3_.codeType
                        AND b0_.mentionBac_id = m2_.codeMention 

Well, that doesn't look correct. Because (except for the d1 - e5 join), we don't have a way to join the other 4 tables. All 4 are to be joined with the b4 which has to be last (as we want to LEFT JOIN it.) This observation leaves with one choice:

FROM 
  TypeBac          t3_  CROSS JOIN 
  MentionBac       m2_  CROSS JOIN 
  Delegation       d1_  INNER JOIN 

  EtablissementBac e5_   ON e5_.delegation_id = d1_.codeLieu 
                        CROSS JOIN 
  Bachelier        b4_  
                        LEFT JOIN
  Bac              b0_   ON b0_.bachelier_cne = b4_.cne 
                        AND b0_.etabBac_id = e5_.codeLieu 
                        AND b0_.typeBac_id = t3_.codeType
                        AND b0_.mentionBac_id = m2_.codeMention 

Try changing the FROM clause as above. The only other option I see is the removal of the CROSS JOIN Bachelier b4_, as no column of this table is included in the SELECT.


After more comments and a request, here is one final trial:

SELECT COUNT( b0_.id ) AS sclr0, 
       d1_.codeLieu AS codeLieu1, d1_.nomLieu AS nomLieu2, 
         d1_.lngLieu AS lngLieu3, d1_.latLieu AS latLieu4, 
       m2_.libelleMention AS libelleMention5, 
       t3_.libelleType AS libelleType6
FROM 
  TypeBac          t3_  
    CROSS JOIN 
  MentionBac       m2_  
    CROSS JOIN 
  Delegation       d1_  
    INNER JOIN 
  EtablissementBac e5_   ON e5_.delegation_id = d1_.codeLieu 
    LEFT JOIN
  Bac              b0_   ON b0_.etabBac_id = e5_.codeLieu 
                        AND b0_.typeBac_id = t3_.codeType
                        AND b0_.mentionBac_id = m2_.codeMention 
WHERE m2_.codeMention IN ('TB',  'B')
  AND t3_.codeType IN ('114',  '129')
  AND EXISTS
        ( SELECT *
          FROM Bac b
          WHERE b.etabBac_id = e5_.codeLieu 
            AND b.typeBac_id = t3_.codeType
            AND b.mentionBac_id = m2_.codeMention 
        )
GROUP BY 
    d1_.codeLieu, 
    m2_.codeMention, m2_.libelleMention, 
    t3_.codeType, t3_.libelleType, t3_.abbrType ;
share|improve this answer
    
Thank you @ypercube for all those explanations, it almost solves my problem. There's just one more little problem. For the nomLieu2 where all sclr0 are equal to zero, I don't want them to appear. Can you help me with that ? –  Sherlock May 10 '13 at 21:37
    
I'm confused now, the description in the question says that all this trouble is exactly for showing up those rows. –  ypercube May 10 '13 at 21:40
    
Your code solves my problem in a way, but there's something extra : all the nomLieu2 of the table appear. What I want to appear is only those where at least one sclr0 is not null. All the nomLieu2 where all sclr0 are null can be avoided. Do you get my point ? –  Sherlock May 10 '13 at 21:47
    
I mean now it's already great with the code you suggested. But I want to exclude the nomLieu2 where all sclr0 are null. But if for one nomLieu2, there's one (at least) sclr0 which is not null, I want all the other sclr0 to be put to zero. –  Sherlock May 10 '13 at 21:49
    
would you please help me with the last thing ? –  Sherlock May 11 '13 at 17:18
show 7 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.