 |  |  |  |  |  |  |  |
Seemingly Simple Issue
Please support our MS SQL advertiser: Intel Parallel Studio Home
 |
Hi,
I created to data tables to hold information provided through peer reviews in the company that I work for. One table has 5 different levels of quality (unsatisfactory, meets some requirements, meets requirements, exceeds expectations greatly exceeds expectations), and another table that holds the data for each peer review (the person who is being reviewed, the time, comments, and a number to be used as a key into the quality table for each criteria). For example, one row might read: userID = 1, Planning=5 (greatly exceeds expectations), Decision Making = 3, Time Management =2, Comments = 'So-and-so has problems with...', time/date =June 20, 2007, 10:18.
The problem is, because there are numerous criteria for each peer review, I cannot figure out how to select a quality for each criteria.
If I do an INNER JOIN ON planning = qualityID and decisionMaking = qualityID... I don't get any data.
I created to data tables to hold information provided through peer reviews in the company that I work for. One table has 5 different levels of quality (unsatisfactory, meets some requirements, meets requirements, exceeds expectations greatly exceeds expectations), and another table that holds the data for each peer review (the person who is being reviewed, the time, comments, and a number to be used as a key into the quality table for each criteria). For example, one row might read: userID = 1, Planning=5 (greatly exceeds expectations), Decision Making = 3, Time Management =2, Comments = 'So-and-so has problems with...', time/date =June 20, 2007, 10:18.
The problem is, because there are numerous criteria for each peer review, I cannot figure out how to select a quality for each criteria.
If I do an INNER JOIN ON planning = qualityID and decisionMaking = qualityID... I don't get any data.
Hm, I think my first message was probably very unclear. My problem seems to be that I'm working with a one-to-many relationship (I only know how to work with one-to-one relationships).
Basically, how do I write a SELECT command for two tables with a one-to-many relationship.
Basically, how do I write a SELECT command for two tables with a one-to-many relationship.
Attatched is the diagram of the relationship. I want to display a report that has a qualityDescription for each of the columns in the peerReview table that are related to the peerReviewQuality table (planning, decision, timeManage, problem, etc.)
Looks like I found the answer somewhere else. I just had to use LEFT JOIN to the peerReviewQuality table for each column in the peerReview table:
SELECT peerReview.employeeID, Qplanning.qualityDescription, Qdecision.qualityDescription, QtimeManage.qualityDescription, Qproblem.qualityDescription, Qtechnical.qualityDescription, Qflexibility.qualityDescription, Qmotivation.qualityDescription, Qreliability.qualityDescription, Qteamwork.qualityDescription, QwrittenComm.qualityDescription, QoralComm.qualityDescription, QresultsContributes.qualityDescription, peerReview.comments, peerReview.timeDate FROM peerReview LEFTJOIN peerReviewQuality AS Qplanning ON peerReview.planning = Qplanning.qualityID LEFTJOIN peerReviewQuality AS Qdecision ON peerReview.decision = Qdecision.qualityID LEFTJOIN peerReviewQuality AS QtimeManage ON peerReview.timeManage = QtimeManage.qualityID LEFTJOIN peerReviewQuality AS Qproblem ON peerReview.problem = Qproblem.qualityID LEFTJOIN peerReviewQuality AS Qtechnical ON peerReview.technical = Qtechnical.qualityID LEFTJOIN peerReviewQuality AS Qflexibility ON peerReview.flexibility = Qflexibility.qualityID LEFTJOIN peerReviewQuality AS Qmotivation ON peerReview.motivation = Qmotivation.qualityID LEFTJOIN peerReviewQuality AS Qreliability ON peerReview.reliability = Qreliability.qualityID LEFTJOIN peerReviewQuality AS Qteamwork ON peerReview.teamwork = Qteamwork.qualityID LEFTJOIN peerReviewQuality AS QwrittenComm ON peerReview.writtenComm = QwrittenComm.qualityID LEFTJOIN peerReviewQuality AS QoralComm ON peerReview.oralComm = QoralComm.qualityID LEFTJOIN peerReviewQuality AS QresultsContributes ON peerReview.resultsContributes = QresultsContributes.qualityID
Last edited by ChimpusDupus; Jun 22nd, 2007 at 10:47 am.
|
Similar Threads
- Help with a seemingly simple program (C++)
- vBulletin mod_rewrite (PHP)
- Problems With Xp Boot Loader (Windows NT / 2000 / XP)
- Need help writing a program (C++)
- have you ever abused ur powers? (Geeks' Lounge)
- Mac Mouse Problem! Help me out? (OS X)
Other Threads in the MS SQL Forum
- Previous Thread: Database Query Help
- Next Thread: insert multiple rows in database
| Thread Tools | Search this Thread |
You need to join our community in order to build your list of favorite forums. You can visit the forum index for a listing of all forums.
Related Forum Features
Latest MS SQL Posts
- Today's Posts
- Unanswered Threads
