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 2008 Forums
 Transact-SQL (2008)
 using count across multiple tables

Author  Topic 

kcarbone1970
Yak Posting Veteran

52 Posts

Posted - 2013-08-11 : 10:44:34
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 [url]http://www.studentmanagementsolutions.com/pic1.png[/url]

Link2 [url]http://www.studentmanagementsolutions.com/pic2.png[/url]


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

30421 Posts

Posted - 2013-08-11 : 15:12:26
FULL JOIN, or UNION ALL?



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

kcarbone1970
Yak Posting Veteran

52 Posts

Posted - 2013-08-12 : 02:29:32
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
Aged Yak Warrior

545 Posts

Posted - 2013-08-12 : 02:52:19

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
Yak Posting Veteran

52 Posts

Posted - 2013-08-13 : 01:00:38
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-13 : 02:04:17
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
Yak Posting Veteran

52 Posts

Posted - 2013-08-13 : 22:11:41
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

30421 Posts

Posted - 2013-08-14 : 02:14:43
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
Yak Posting Veteran

52 Posts

Posted - 2013-08-14 : 02:20:41
Cool...that did it...thanks so much!!!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-14 : 02:23:01
Did you try my suggestion?


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

kcarbone1970
Yak Posting Veteran

52 Posts

Posted - 2013-08-14 : 02:36:09
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
Yak Posting Veteran

52 Posts

Posted - 2013-08-14 : 03:00:06
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

30421 Posts

Posted - 2013-08-14 : 03:46:41
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

30421 Posts

Posted - 2013-08-14 : 03:48:37
[code]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]%'
)[/code]


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

- Advertisement -