JOINs & COUNT() - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

JOINs & COUNT()

Author

Topic

younesagma
Starting Member

2 Posts

Posted - 2013-05-10 : 08:13:01

Hi, 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

What I need is to get all the possible combinations of codeLieu1, nomLieu2, libelleMention5, libelleType6, and then the corresponding column sclr0 (which contains the number if it exists, and 0 is it is null).

For the moment, what I get is only the rows with sclr0 different from null.

Can you help me please ?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-10 : 09:38:56

[code]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, d1_.nomLieu, m2_.codeMention, m2_.libelleMention, t3_.libelleType[/code]

--
Chandu

younesagma
Starting Member

2 Posts

Posted - 2013-05-10 : 09:49:52

It doesn't work actually.

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-13 : 01:17:00

you may be better off posting some sample data and explain what you want. Otherwie its quite hard to make out what you're looking at from above query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources