SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 JOINs & COUNT()
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

younesagma
Starting Member

2 Posts

Posted - 05/10/2013 :  08:13:01  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 05/10/2013 :  09:38:56  Show Profile  Reply with Quote
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


--
Chandu
Go to Top of Page

younesagma
Starting Member

2 Posts

Posted - 05/10/2013 :  09:49:52  Show Profile  Reply with Quote
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).
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 05/13/2013 :  01:17:00  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000