select with join problem - 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

SQL Server 2005 Forums

Transact-SQL (2005)

select with join problem

Author

Topic

mixalis
Starting Member

8 Posts

Posted - 2008-09-11 : 05:51:04

hi all, I have some problem with the following:

tables
skillscard
centre
pivot skillscard_centre(centre_fk,skillscard_fk)

I want to have an output like the following

centre1 skillscard1 count(skillscard1)
centre1 skillscard2 count(skillscard2)
centre1 skillscard3 count(skillscard3)
centre2 skillscard1 count(skillscard4)

note that if a centre1 does not have skillscard2 I still want to appear in the output with count = 0

can you help?...


      select 

      TestCentre.tc_pk, TestCentre.brand_name,SkillsCard.name sc_name, count(*) as cnt
--•	Test Center Code
--•	Test Center Name
--•	Candidate Card Description
--•	Count

      FROM SC_TC WITH (NOLOCK)
      INNER JOIN TestCentre WITH (NOLOCK) ON sc_tc.tc_fk = TestCentre.tc_pk
      INNER JOIN SkillsCard WITH (NOLOCK) On sc_tc.sc_fk = SkillsCard.pk
	 GROUP BY SkillsCard.name,TestCentre.brand_name,TestCentre.tc_pk
		having (count(*) > 1)

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-09-11 : 06:01:02

try this


SELECT 
	TestCentre.tc_pk, 
	TestCentre.brand_name,
	SkillsCard.name sc_name, 
	count(*) as cnt
	--•	Test Center Code
	--•	Test Center Name
	--•	Candidate Card Description
	--•	Count
FROM SkillsCard
LEFT JOIN SC_TC WITH (NOLOCK) ON sc_tc.sc_fk = SkillsCard.pk
LEFT JOIN TestCentre WITH (NOLOCK) ON sc_tc.tc_fk = TestCentre.tc_pk
GROUP BY SkillsCard.name,TestCentre.brand_name,TestCentre.tc_pk
HAVING (COUNT(*) > 1)

mixalis
Starting Member

8 Posts

Posted - 2008-09-11 : 06:18:28

with left join


centre1	card1	10
centre1	card2	1118
centre1	card3	118
centre1	card4	143
centre1	card5	10
centre1	card6	22

with inner join


centre1	card1	10
centre1	card2	1118
centre1	card3	118
centre1	card4	143
centre1	card5	10
centre1	card6	22

the results are identical, I have tried this solution too...
Why is this happening??

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-09-11 : 06:21:25

remove having clause


SELECT 
	TestCentre.tc_pk, 
	TestCentre.brand_name,
	SkillsCard.name sc_name, 
	count(SkillsCard.pk) as cnt
	--•	Test Center Code
	--•	Test Center Name
	--•	Candidate Card Description
	--•	Count
FROM SkillsCard
LEFT JOIN SC_TC WITH (NOLOCK) ON sc_tc.sc_fk = SkillsCard.pk
LEFT JOIN TestCentre WITH (NOLOCK) ON sc_tc.tc_fk = TestCentre.tc_pk
GROUP BY SkillsCard.name,TestCentre.brand_name,TestCentre.tc_pk
--HAVING (COUNT(SkillsCard.pk) > 1)

mixalis
Starting Member

8 Posts

Posted - 2008-09-11 : 06:34:11

still the same result..I understand you mean though thanx for the effort...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources