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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 using count across multiple tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kcarbone1970
Starting Member

USA
31 Posts

Posted - 08/11/2013 :  10:44:34  Show Profile  Reply with Quote
I produced a query count originally pulling data from a single table (HIS), recently i was told that i must include data from another table. Combining the results within a single query has proven to be a table join nightmare of confusion. So link 1 shows the results prior to the joining of the additional table, and link 2 shows the results using additional table (SSS) however it only produces results from new table (SSS)??? I need the combined results. So my results should be the combined totals of the two tables?? Any help greatly appreciated!!
Link1 http://www.studentmanagementsolutions.com/pic1.png

Link2 http://www.studentmanagementsolutions.com/pic2.png


Below is some code I've been playing with

SELECT        COUNT(CASE WHEN CRS.U1 = 'A' THEN HIS.CN END) AS HISTORY, COUNT(CASE WHEN CRS.U1 = 'B' THEN HIS.CN END) AS ELA, 
                         COUNT(CASE WHEN CRS.U1 = 'C' THEN HIS.CN END) AS MATH, COUNT(CASE WHEN CRS.U1 = 'D' THEN HIS.CN END) AS SCIENCE, 
                         COUNT(CASE WHEN CRS.U1 = 'E' THEN HIS.CN END) AS FL, COUNT(CASE WHEN CRS.U1 = 'F' THEN HIS.CN END) AS VA, 
                         COUNT(CASE WHEN CRS.U1 = 'G' THEN HIS.CN END) AS Prep, CASE WHEN COUNT(CASE WHEN CRS.U1 = 'A' THEN HIS.CN END) >= 6 AND 
                         COUNT(CASE WHEN CRS.U1 = 'B' THEN HIS.CN END) >= 6 AND COUNT(CASE WHEN CRS.U1 = 'C' THEN HIS.CN END) >= 6 AND 
                         COUNT(CASE WHEN CRS.U1 = 'D' THEN HIS.CN END) >= 6 AND COUNT(CASE WHEN CRS.U1 = 'E' THEN HIS.CN END) >= 6 AND 
                         COUNT(CASE WHEN CRS.U1 = 'F' THEN HIS.CN END) >= 6 AND COUNT(CASE WHEN CRS.U1 = 'G' THEN HIS.CN END) 
                         >= 6 THEN 'Yes' ELSE 'No' END AS [A-G Fulfillment]
FROM            CRS FULL OUTER JOIN
                         HIS ON CRS.CN = HIS.CN FULL OUTER JOIN
                         STU ON HIS.PID = STU.ID FULL OUTER JOIN
                         SSS ON STU.SC = SSS.SC AND STU.SN = SSS.SN AND CRS.CN = SSS.CN
WHERE        (STU.ID = @ID)

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 08/11/2013 :  15:12:26  Show Profile  Visit SwePeso's Homepage  Reply with Quote
FULL JOIN, or UNION ALL?



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

kcarbone1970
Starting Member

USA
31 Posts

Posted - 08/12/2013 :  02:29:32  Show Profile  Reply with Quote
ok so to simplify this I tried
select * from HIS where PID = 4068968

and it produces 12 results that's correct..AND
SELECT        SSS.CN AS SSScn, STU.ID
FROM            SSS INNER JOIN
                         STU ON SSS.SN = STU.SN
WHERE        (STU.ID = 4068968)

produces 6 results I need these combined in a single result of 18??

I tried
SELECT DISTINCT SSS.CN AS SSScn, STU.ID, HIS.CN
FROM            SSS INNER JOIN
                         STU ON SSS.SN = STU.SN INNER JOIN
                         HIS ON STU.ID = HIS.PID
WHERE        (STU.ID = 4068968)

and it produces 72 records..WHAT am I doing wrong?? please enlighten the noob!!

Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
280 Posts

Posted - 08/12/2013 :  02:52:19  Show Profile  Reply with Quote

for this sample , your HIS tbl , has 12 rows on PID=4068968

your query has 6 rows on ID=4068968

joining this together , will result in 72 rows (12*6)

INNER JOIN  HIS ON STU.ID = HIS.PID 


maybe add some extra join condition or change the line


INNER JOIN  (select PID,CN from HIS group by PID,CN) HIS ON STU.ID = HIS.PID 


S


Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page

kcarbone1970
Starting Member

USA
31 Posts

Posted - 08/13/2013 :  01:00:38  Show Profile  Reply with Quote
thanks for responding, I tried group by still get 72... what i need is to select only the 18 records, 12 from HIS and 6 from SSS to appear exclusively in one table
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 08/13/2013 :  02:04:17  Show Profile  Reply with Quote
Let us know the number of records for the below two queries separately....
-- Query1
SELECT SSS.CN AS SSScn, STU.ID
FROM SSS
INNER JOIN STU ON SSS.SN = STU.SN
WHERE (STU.ID = 4068968)
UNION
SELECT CN, PID from HIS
where PID = 4068968


-- Query2:
SELECT SSS.CN AS SSScn, STU.ID
FROM SSS
INNER JOIN STU ON SSS.SN = STU.SN
WHERE (STU.ID = 4068968)
UNION ALL
SELECT CN, PID from HIS
where PID = 4068968

--
Chandu
Go to Top of Page

kcarbone1970
Starting Member

USA
31 Posts

Posted - 08/13/2013 :  22:11:41  Show Profile  Reply with Quote
So they both produced 18 results! cool!....now I just need to wrap that someway in my original query???

I was trying something like this but get errors

SELECT        
COUNT(CASE WHEN CRS.U1 = 'A' THEN HIS.CN END) AS HISTORY, 
COUNT(CASE WHEN CRS.U1 = 'B' THEN HIS.CN END) AS ELA, 
COUNT(CASE WHEN CRS.U1 = 'C' THEN HIS.CN END) AS MATH, 
COUNT(CASE WHEN CRS.U1 = 'D' THEN HIS.CN END) AS SCIENCE, 
COUNT(CASE WHEN CRS.U1 = 'E' THEN HIS.CN END) AS FL, 
COUNT(CASE WHEN CRS.U1 = 'F' THEN HIS.CN END) AS VA, 
COUNT(CASE WHEN CRS.U1 = 'G' THEN HIS.CN END) AS Prep, 

CASE WHEN 
COUNT(CASE WHEN CRS.U1 = 'A' THEN HIS.CN END) >= 6 AND 
COUNT(CASE WHEN CRS.U1 = 'B' THEN HIS.CN END) >= 6 AND 
COUNT(CASE WHEN CRS.U1 = 'C' THEN HIS.CN END) >= 6 AND 
COUNT(CASE WHEN CRS.U1 = 'D' THEN HIS.CN END) >= 6 AND 
COUNT(CASE WHEN CRS.U1 = 'E' THEN HIS.CN END) >= 6 AND 
COUNT(CASE WHEN CRS.U1 = 'F' THEN HIS.CN END) >= 6 AND 
COUNT(CASE WHEN CRS.U1 = 'G' THEN HIS.CN END) >= 6 

THEN 'Yes' ELSE 'No' END AS [On Target?]
FROM            SSS INNER JOIN
                         STU ON SSS.SN = STU.SN INNER JOIN
                         CRS ON SSS.CN = CRS.CN
WHERE        (STU.ID = 4068968)
UNION 
SELECT        
COUNT(CASE WHEN CRS.U1 = 'A' THEN HIS.CN END) AS HISTORY, 
COUNT(CASE WHEN CRS.U1 = 'B' THEN HIS.CN END) AS ELA, 
COUNT(CASE WHEN CRS.U1 = 'C' THEN HIS.CN END) AS MATH, 
COUNT(CASE WHEN CRS.U1 = 'D' THEN HIS.CN END) AS SCIENCE, 
COUNT(CASE WHEN CRS.U1 = 'E' THEN HIS.CN END) AS FL, 
COUNT(CASE WHEN CRS.U1 = 'F' THEN HIS.CN END) AS VA, 
COUNT(CASE WHEN CRS.U1 = 'G' THEN HIS.CN END) AS Prep, 

CASE WHEN 
COUNT(CASE WHEN CRS.U1 = 'A' THEN HIS.CN END) >= 6 AND 
COUNT(CASE WHEN CRS.U1 = 'B' THEN HIS.CN END) >= 6 AND 
COUNT(CASE WHEN CRS.U1 = 'C' THEN HIS.CN END) >= 6 AND 
COUNT(CASE WHEN CRS.U1 = 'D' THEN HIS.CN END) >= 6 AND 
COUNT(CASE WHEN CRS.U1 = 'E' THEN HIS.CN END) >= 6 AND 
COUNT(CASE WHEN CRS.U1 = 'F' THEN HIS.CN END) >= 6 AND 
COUNT(CASE WHEN CRS.U1 = 'G' THEN HIS.CN END) >= 6 


THEN 'Yes' ELSE 'No' END AS [On Target?]
HIS.CN, HIS.PID
FROM            HIS INNER JOIN
                         CRS ON HIS.CN = CRS.CN
WHERE        (HIS.PID = 4068968)
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 08/14/2013 :  02:14:43  Show Profile  Visit SwePeso's Homepage  Reply with Quote
This?
WITH cteSource(CN, U1)
AS (
	SELECT		r.CN,
			r.U1
	FROM		dbo.SSS AS s 
	INNER JOIN	dbo.STU AS t ON t.SN = s.SN 
	INNER JOIN	dbo.CRS AS r ON r.CN = s.CN
	WHERE		t.ID = 4068968

	UNION ALL

	SELECT		r.CN,
			r.U1
	FROM		dbo.HIS AS i 
	INNER JOIN	dbo.CRS AS r ON r.CN = i.CN
	WHERE		i.PID = 4068968
)
SELECT	p.A AS HISTORY,
	p.B AS ELA,
	p.C AS MATH,
	p.D AS SCIENCE,
	p.E AS FL,
	p.F AS VA,
	p.G AS Prep,
	CASE
		WHEN p.A >= 6 AND p.B >= 6 AND p.C >= 6 AND p.D >= 6 AND p.E >= 6 AND p.F >= 6 AND p.G >= 6 THEN 'Yes' 
		ELSE 'No' 
	END AS [On Target?]
FROM	cteSource AS s
PIVOT	(
		COUNT(s.CN)
		FOR s.U1 IN ([A], [B], [C], [D], [E], [F], [G])
	) AS p;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

kcarbone1970
Starting Member

USA
31 Posts

Posted - 08/14/2013 :  02:20:41  Show Profile  Reply with Quote
Cool...that did it...thanks so much!!!

Edited by - kcarbone1970 on 08/14/2013 02:22:28
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 08/14/2013 :  02:23:01  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Did you try my suggestion?


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

kcarbone1970
Starting Member

USA
31 Posts

Posted - 08/14/2013 :  02:36:09  Show Profile  Reply with Quote
Thanks again, I really appreciate your help. the complexity of your query intimidates me a little....but adding another condition? like what I have here, where/how would that go?


SELECT        COUNT(CASE WHEN CRS.U1 = 'A' AND (HIS.MK NOT LIKE '%F%') AND (HIS.MK NOT LIKE '%D%') THEN HIS.CN END) AS HISTORY, 
                         COUNT(CASE WHEN CRS.U1 = 'B' AND (HIS.MK NOT LIKE '%F%') AND (HIS.MK NOT LIKE '%D%') THEN HIS.CN END) AS ELA, 
                         COUNT(CASE WHEN CRS.U1 = 'C' AND (HIS.MK NOT LIKE '%F%') AND (HIS.MK NOT LIKE '%D%') THEN HIS.CN END) AS MATH, 
...

Go to Top of Page

kcarbone1970
Starting Member

USA
31 Posts

Posted - 08/14/2013 :  03:00:06  Show Profile  Reply with Quote
OK I think I got it that one too...I added the additional condition here..Thanks again, you made a lot of people happy!

WITH cteSource(CN, U1)
AS (
	SELECT		r.CN,
			r.U1
	FROM		dbo.SSS AS s 
	INNER JOIN	dbo.STU AS t ON t.SN = s.SN 
	INNER JOIN	dbo.CRS AS r ON r.CN = s.CN
	WHERE		t.ID = 4068968

	UNION ALL

	SELECT		r.CN,
			r.U1
	FROM		dbo.HIS AS i 
	INNER JOIN	dbo.CRS AS r ON r.CN = i.CN
	WHERE		i.PID = 4068968 AND (i.MK NOT LIKE '%F%') AND (i.MK NOT LIKE '%D%')
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 08/14/2013 :  03:46:41  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You're welcome!


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 08/14/2013 :  03:48:37  Show Profile  Visit SwePeso's Homepage  Reply with Quote
WITH cteSource(CN, U1)
AS (
	SELECT		r.CN,
			r.U1
	FROM		dbo.SSS AS s 
	INNER JOIN	dbo.STU AS t ON t.SN = s.SN 
	INNER JOIN	dbo.CRS AS r ON r.CN = s.CN
	WHERE		t.ID = 4068968

	UNION ALL

	SELECT		r.CN,
			r.U1
	FROM		dbo.HIS AS i 
	INNER JOIN	dbo.CRS AS r ON r.CN = i.CN
	WHERE		i.PID = 4068968
			AND i.MK NOT LIKE '%[FD]%'
)



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
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.14 seconds. Powered By: Snitz Forums 2000