SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


help count that involves mulitple table joins with foreign keys


help count that involves mulitple table joins with foreign keys

Author
Message
kristofour
kristofour
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 37
I am trying to get a count from two separate tables that are joined via foreign key, it only seems to be counting from the HIS table I need results from both the HIS and GRD



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



Thanks in advance


kristofour
kristofour
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 37
so if I union it separates the correct values in two rows....so how to sum the rows into one and display only one?


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


LutzM
LutzM
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24091 Visits: 13559
What exactly aore you trying to do?

The first query you posted limits the number of rows since you perform an inner join on CRS, HIS, and STU and use this result in an outer join on GRD(SN).
Since the column names are all but descriptive it's hard to figure the business logic behind it.
Just guessing I would "translate"
CN = course name
SN = student name
STU.ID = student id
HIS.PID = person id (aka student id).

If that's the case then the database design itself is inkonsistent: What would you do if there are two students with the name John Doe in one course? One received an 'A' and the other one an 'F'. How would your result set look like?

Therefore I'd like to ask you to provide the table definition together with some sample data and your expected result based on those data. For details onhow to do that please see the firstlink in my signature.



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
kristofour
kristofour
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 37
I didn't design the database, but unfortunately for whatever reason that is causing the headache here (at least for me) is that they use two identifiers! SN (student number) which is not unique and ID (studentID) which is unique but not present on all the tables sometimes only SN is present, and in this case the table I am trying to join only has SN so I have to join it to the STU (student) table which has both ID and SN...so the HIS (history table) has there final grades and uses ID...the GRD (grade) table uses SN which shows present or progress report grades they want them both added up categorically (A-G) which are college prereq courses and displaying only courses with a C- or better. So I am trying to count up all the A-G courses that they've taken and presently taking....I think I have it figured out....I am using this...I don't know if there is a better way or what... but seems ok?



DECLARE @ID INT = 4056102


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
(

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search