Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1130
  • Last Modified:

SQL Syntax on Distinct records

My site's basic database structure is as follows:

facultyinfo table contains faculty information

training table contains inservice training opportunities

participants table contains the FacultyID from the facultyinfo table and the TrainingID from the training table along with a completion status (No, CEU, Graduate, Undergraduate)

I have queries that total the number of hours completed if the completion status is (CEU, Graduate, Undergraduate). Since the query uses an SQL Select Disting Row statement it will show two records it is possible to show two, or more, records if some courses were taken for CEUs, some for Graduate credit and some for Undergraduate credit.  Is there a better way to create this query so I can get the total number of hours completed but aggregate each faculty as only one record.  My current report shows a faculty member two, or more, times if they have more than one completion status (CEU, Graduate, Undergraduate).  I understand this is because the record is not seen as distinct since it contains a different completion status.  

I'm not sure whether to address this on the database design side or if I can modify the SQL query.
0
bowsere
Asked:
bowsere
  • 4
  • 3
  • 2
  • +1
1 Solution
 
jrramCommented:
So, you want to get the total number of hours completed per faculty regardless of the completion status?
0
 
bowsereAuthor Commented:
Kind of.  I'm already getting that by querying for records that have Graduate, Undergraduage or CEU set in the completion field.  However since the participants table contains multiple TrainingIDs and multiple TeacherIDs for one teacher and the completion status is Graduate for some, undergraduate for others, CEU for others and No for others the select distince row will return multiple records for one teacher.  Below is an example of the participants table data:

TrainingID  TeacherID  Completion
4051987       1567           CEU
4052879       1567           Graduate
4050098       1567           Undergraduate
4051112       1567           No

If I query the database for disting row where Completion <> "No" I will get three separate records for the teacher with TeacherID 1567.  By the way, I'm getting my Sum of hours by joining the participants table witht he Training table.  The training table contains the field with the number of hours per inservice.
0
 
jrramCommented:
Here's an example.  Let's say your tables are designed like:

Table faculty
"facultyID"                      "name"
1            Cookie Monster
2            Bert
3            Ernie
4            Snufflelufagus

Table courses
"courseID"                  "facultyID"                     "status"                "hours"
1            1            CEU            7
2            1            Graduate                      9
3            1            Undergraduate      11
1            2            Graduate"                      10
1            3            No            11
2            3            Graduate                      12
2            2            Undergraduate      10
4            1            CEU            30
3            2            CEU            10

Using this query...

SELECT courses.facultyID, faculty.name,  SUM (courses.Hours) As totalHours FROM courses, faculty WHERE courses.status <> 'No' And faculty.facultyID = courses.facultyID GROUP BY courses.facultyID, faculty.name UNION SELECT a.facultyID, a.name, 0 as totalHours FROM faculty a WHERE NOT EXISTS (SELECT * FROM courses b WHERE a.facultyID = b.facultyID)

It would give you:

"facultyID"                     "name"                     "totalHours"
1            Cookie Monster      57
2            Bert            30
3            Ernie            12
4            Snufflelufagus      0

HTH
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jrramCommented:
Hopefully, this format better, but adding the courses table

--Table faculty--
"facultyID"   "name"
1                Cookie Monster
2                Bert
3                Ernie
4                Snufflelufagus

--Table Courses--
"courseID"     "name"          "hours"
1                   Sewing               3
2                   Fly Fishing     5
3                   Art                2
4                   Math             4

--Table Training--
"courseID"  "facultyID"     "status"                 "hours"
1               1                   CEU                      7
2               1                   Graduate               9
3               1                   Undergraduate      11
1               2                   Graduate              10
1               3                   No                       11
2               3                   Graduate              12
2               2                   Undergraduate      10
4               1                   CEU                     30
3               2                   CEU                     10

Using:

SELECT Training.facultyID, faculty.name,  SUM (Courses.Hours) As totalHours FROM Training, Courses, faculty WHERE Training.status <> 'No' And faculty.facultyID = Training.facultyID And  Training.courseID = Courses.courseID GROUP BY Training.facultyID, faculty.name UNION SELECT a.facultyID, a.name, 0 as totalHours FROM faculty a WHERE NOT EXISTS (SELECT * FROM Training b WHERE a.facultyID = b.facultyID);

You'd get:

"facultyID"     "name"                 "totalHours"
1                  Cookie Monster      14
2                  Bert                      10
3                  Ernie                    5
4                  Snufflelufagus       0
0
 
jrramCommented:
Sorry, the Training table should be:

--Table Training--
"courseID"  "facultyID"     "status"
1               1                   CEU
2               1                   Graduate
3               1                   Undergraduate
1               2                   Graduate
1               3                   No
2               3                   Graduate
2               2                   Undergraduate
4               1                   CEU
3               2                   CEU
0
 
Anthony PerkinsCommented:
For the record, I suspect the questioner is using MS Access, so he/she may trip up on the use of EXISTS.
0
 
WMIFCommented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.  I will make a recommendation to the moderators on its resolution in a week or two.  I appreciate any comments that would help me to make a recommendation.

Unless it is clear to me that the question has been answered I will recommend delete without refund of points.  It is possible that a Grade less than A will be given if no expert makes a case for an A grade.  It is assumed that any participant not responding to this request is no longer interested in its final disposition.

If the asker does not know how to close the question, the options are here:
http://www.experts-exchange.com/help.jsp#hs5

WMIF
EE Cleanup Volunteer
0
 
WMIFCommented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.  I will leave the following recommendation for this question in the Cleanup topic area:
Split - jrram,acperkins

Any objections should be posted here in the next 4 days.  After that time, the question will be closed.

WMIF
EE Cleanup Volunteer
0
 
Anthony PerkinsCommented:
>>Split - jrram,acperkins<<
Thanks, but I did not make any significant contributions to this thread.
0
 
bowsereAuthor Commented:
Sorry for abandoning this question.  I had gotten busy on several other projects and forgotten I had even posted this until I received the abandoned notices.  I awarded the points to jrram.  Thanks to acperkins on the note about EXISTS and MS Access.  Someday I need to convert this all over to MS SQL.
0
 
Anthony PerkinsCommented:
>>I had gotten busy on several other projects and forgotten I had even posted<<
Here are three more questions, I suspect you may have overlooked:

1 11/09/2005 500 SQL query that pulls all records when a ...  Open Microsoft SQL Server
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21624874.html

2 02/08/2005 250 Flash frontend ASP w/Access DB backend i...  Open Macromedia Flash
http://www.experts-exchange.com/Web/WebDevSoftware/Flash/Q_21306722.html

3 10/22/2005 250 Create a "Page Footer" and control on wh...  Open Active Server Pages (ASP)
http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_21604394.html
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now