help with table joins and foreign keys

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)

help with table joins and foreign keys

Author

Topic

kcarbone1970
Yak Posting Veteran

52 Posts

Posted - 2013-10-10 : 20:56:21

So I had this query that was working great counting data only from one table(HIS)...now I need to count and combine from another table (GRD) that has a foreign key. I added the "OR" statement and did a left join it counts the data from HIS table OK but I cant seem to get it to include the GRD. Any help appreciated.


DECLARE @ID INT = 4043300;
SELECT       
    HISTORY, ELA, MATH, SCIENCE, FL, VA, Prep,
CASE WHEN HISTORY >= 0 AND ELA >= 1 AND MATH >= 1 AND SCIENCE >= 1 AND  FL >= 1 AND VA >= 1 AND Prep >= 1         
THEN 'Yes'         
ELSE 'No' END AS [On Target?]    
FROM 

(SELECT 
COUNT(CASE WHEN CRS.U1 = 'A' AND (HIS.MK NOT LIKE '%FD%') OR (GRD.M1 NOT LIKE '%FD%') THEN STU.ID END) AS HISTORY,
COUNT(CASE WHEN CRS.U1 = 'B' AND (HIS.MK NOT LIKE '%FD%') OR (GRD.M1 NOT LIKE '%FD%') THEN STU.ID END) AS ELA,        
COUNT(CASE WHEN CRS.U1 = 'C' AND (HIS.MK NOT LIKE '%FD%') OR (GRD.M1 NOT LIKE '%FD%') THEN STU.ID END) AS MATH,        
COUNT(CASE WHEN CRS.U1 = 'D' AND (HIS.MK NOT LIKE '%FD%') OR (GRD.M1 NOT LIKE '%FD%') THEN STU.ID END) AS SCIENCE,        
COUNT(CASE WHEN CRS.U1 = 'E' AND (HIS.MK NOT LIKE '%FD%') OR (GRD.M1 NOT LIKE '%FD%') THEN STU.ID END) AS FL,        
COUNT(CASE WHEN CRS.U1 = 'F' AND (HIS.MK NOT LIKE '%FD%') OR (GRD.M1 NOT LIKE '%FD%') THEN STU.ID END) AS VA,        
COUNT(CASE WHEN CRS.U1 = 'G' AND (HIS.MK NOT LIKE '%FD%') OR (GRD.M1 NOT LIKE '%FD%') THEN STU.ID END) AS Prep    
FROM CRS 

INNER JOIN
HIS ON CRS.CN = HIS.CN 
INNER JOIN
STU ON HIS.PID = STU.ID 
LEFT OUTER JOIN 
GRD ON CRS.CN = GRD.CN AND dbo.GRD.SN = dbo.STU.SN


WHERE        
STU.ID = @ID) AS derived

So if I union I get the correct values in two rows, I need the sum

DECLARE @ID INT = 4043300

SELECT 
COUNT(CASE WHEN CRS.U1 = 'A' AND (HIS.MK NOT LIKE '%FD%')  THEN STU.ID END) AS HISTORY,
COUNT(CASE WHEN CRS.U1 = 'B' AND (HIS.MK NOT LIKE '%FD%') THEN STU.ID END) AS ELA,        
COUNT(CASE WHEN CRS.U1 = 'C' AND (HIS.MK NOT LIKE '%FD%')  THEN STU.ID END) AS MATH,        
COUNT(CASE WHEN CRS.U1 = 'D' AND (HIS.MK NOT LIKE '%FD%')  THEN STU.ID END) AS SCIENCE,        
COUNT(CASE WHEN CRS.U1 = 'E' AND (HIS.MK NOT LIKE '%FD%') THEN STU.ID END) AS FL,        
COUNT(CASE WHEN CRS.U1 = 'F' AND (HIS.MK NOT LIKE '%FD%')  THEN STU.ID END) AS VA,        
COUNT(CASE WHEN CRS.U1 = 'G' AND (HIS.MK NOT LIKE '%FD%')  THEN STU.ID END) AS Prep    
FROM CRS
CRS INNER JOIN
HIS ON CRS.CN = HIS.CN INNER JOIN
STU ON HIS.PID = STU.ID
WHERE         
STU.ID = @ID


UNION ALL

SELECT 
COUNT(CASE WHEN CRS.U1 = 'A' AND (GRD.M1 NOT LIKE '%FD%') THEN STU.ID END) AS HISTORY,
COUNT(CASE WHEN CRS.U1 = 'B' AND (GRD.M1 NOT LIKE '%FD%') THEN STU.ID END) AS ELA,        
COUNT(CASE WHEN CRS.U1 = 'C' AND (GRD.M1 NOT LIKE '%FD%') THEN STU.ID END) AS MATH,        
COUNT(CASE WHEN CRS.U1 = 'D' AND  (GRD.M1 NOT LIKE '%FD%') THEN STU.ID END) AS SCIENCE,        
COUNT(CASE WHEN CRS.U1 = 'E' AND (GRD.M1 NOT LIKE '%FD%') THEN STU.ID END) AS FL,        
COUNT(CASE WHEN CRS.U1 = 'F' AND (GRD.M1 NOT LIKE '%FD%') THEN STU.ID END) AS VA,        
COUNT(CASE WHEN CRS.U1 = 'G' AND (GRD.M1 NOT LIKE '%FD%') THEN STU.ID END) AS Prep    
FROM CRS 


INNER JOIN
GRD ON dbo.GRD.CN = dbo.CRS.CN
INNER JOIN
STU ON GRD.SN = STU.SN


WHERE         
STU.ID = @ID

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-11 : 01:07:11

[code]
SELECT SUM(HISTORY) AS HISTORY,
SUM(ELA) AS ELA,
SUM(MATH) AS MATH,
SUM(SCIENCE) AS SCIENCE,
SUM(FL) AS FL,
SUM(VA) AS VA,
SUM(Prep) AS Prep
FROM
(
DECLARE @ID INT = 4043300

SELECT
COUNT(CASE WHEN CRS.U1 = 'A' AND (HIS.MK NOT LIKE '%FD%') THEN STU.ID END) AS HISTORY,
COUNT(CASE WHEN CRS.U1 = 'B' AND (HIS.MK NOT LIKE '%FD%') THEN STU.ID END) AS ELA,
COUNT(CASE WHEN CRS.U1 = 'C' AND (HIS.MK NOT LIKE '%FD%') THEN STU.ID END) AS MATH,
COUNT(CASE WHEN CRS.U1 = 'D' AND (HIS.MK NOT LIKE '%FD%') THEN STU.ID END) AS SCIENCE,
COUNT(CASE WHEN CRS.U1 = 'E' AND (HIS.MK NOT LIKE '%FD%') THEN STU.ID END) AS FL,
COUNT(CASE WHEN CRS.U1 = 'F' AND (HIS.MK NOT LIKE '%FD%') THEN STU.ID END) AS VA,
COUNT(CASE WHEN CRS.U1 = 'G' AND (HIS.MK NOT LIKE '%FD%') THEN STU.ID END) AS Prep
FROM CRS
CRS INNER JOIN
HIS ON CRS.CN = HIS.CN INNER JOIN
STU ON HIS.PID = STU.ID
WHERE
STU.ID = @ID

UNION ALL

SELECT
COUNT(CASE WHEN CRS.U1 = 'A' AND (GRD.M1 NOT LIKE '%FD%') THEN STU.ID END) AS HISTORY,
COUNT(CASE WHEN CRS.U1 = 'B' AND (GRD.M1 NOT LIKE '%FD%') THEN STU.ID END) AS ELA,
COUNT(CASE WHEN CRS.U1 = 'C' AND (GRD.M1 NOT LIKE '%FD%') THEN STU.ID END) AS MATH,
COUNT(CASE WHEN CRS.U1 = 'D' AND (GRD.M1 NOT LIKE '%FD%') THEN STU.ID END) AS SCIENCE,
COUNT(CASE WHEN CRS.U1 = 'E' AND (GRD.M1 NOT LIKE '%FD%') THEN STU.ID END) AS FL,
COUNT(CASE WHEN CRS.U1 = 'F' AND (GRD.M1 NOT LIKE '%FD%') THEN STU.ID END) AS VA,
COUNT(CASE WHEN CRS.U1 = 'G' AND (GRD.M1 NOT LIKE '%FD%') THEN STU.ID END) AS Prep
FROM CRS

INNER JOIN
GRD ON dbo.GRD.CN = dbo.CRS.CN
INNER JOIN
STU ON GRD.SN = STU.SN

WHERE
STU.ID = @ID
)r
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

kcarbone1970
Yak Posting Veteran

52 Posts

Posted - 2013-10-11 : 02:43:41

that did it!! thanks!

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-11 : 02:57:36

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources