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.
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 withSELECT 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.CNWHERE (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 |
|
|
kcarbone1970
Yak Posting Veteran
52 Posts |
Posted - 2013-08-12 : 02:29:32
|
ok so to simplify this I triedselect * from HIS where PID = 4068968 and it produces 12 results that's correct..ANDSELECT SSS.CN AS SSScn, STU.IDFROM SSS INNER JOIN STU ON SSS.SN = STU.SNWHERE (STU.ID = 4068968) produces 6 results I need these combined in a single result of 18??I triedSELECT DISTINCT SSS.CN AS SSScn, STU.ID, HIS.CNFROM SSS INNER JOIN STU ON SSS.SN = STU.SN INNER JOIN HIS ON STU.ID = HIS.PIDWHERE (STU.ID = 4068968) and it produces 72 records..WHAT am I doing wrong?? please enlighten the noob!! |
|
|
stepson
Aged Yak Warrior
545 Posts |
Posted - 2013-08-12 : 02:52:19
|
for this sample , your HIS tbl , has 12 rows on PID=4068968your query has 6 rows on ID=4068968joining 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 lineINNER JOIN (select PID,CN from HIS group by PID,CN) HIS ON STU.ID = HIS.PID SCe-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mutsabinWeb |
|
|
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 |
|
|
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....-- Query1SELECT SSS.CN AS SSScn, STU.IDFROM SSS INNER JOIN STU ON SSS.SN = STU.SNWHERE (STU.ID = 4068968)UNION SELECT CN, PID from HIS where PID = 4068968-- Query2:SELECT SSS.CN AS SSScn, STU.IDFROM SSS INNER JOIN STU ON SSS.SN = STU.SNWHERE (STU.ID = 4068968)UNION ALLSELECT CN, PID from HIS where PID = 4068968--Chandu |
|
|
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 errorsSELECT 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.CNWHERE (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.PIDFROM HIS INNER JOIN CRS ON HIS.CN = CRS.CNWHERE (HIS.PID = 4068968) |
|
|
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 sPIVOT ( 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 |
|
|
kcarbone1970
Yak Posting Veteran
52 Posts |
Posted - 2013-08-14 : 02:20:41
|
Cool...that did it...thanks so much!!! |
|
|
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 |
|
|
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, ... |
|
|
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%') |
|
|
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 |
|
|
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 |
|
|
|
|
|
|
|